If I change a date in a date column how do I get the dates below .
I have a column with dates. When I change a date I want all the dates below
the one I've changed to change automatically? How would I do this in Excel?
One way is to enter a date in A1.
In A2 enter this formula:
And copy it down as far as needed.
Please keep all correspondence within the NewsGroup, so all may benefit !
"vpr" <firstname.lastname@example.org> wrote in message
I was wondering if it is possible to set up a formula in a
where you would enter a date ex. 01/01/03) in the first
cell and if the second cell would calculate the next month
Any help would be greatly appreciated!
please repost to microsoft.public.excel
"Dave G." <email@example.com> wrote in message
> I was wondering if it is possible to set up a formula in a
> where you would enter a date ex. 01/01/03) in the first
> cell and if the second cell would ...Help on if statement
I'm trying to develop a two tier 'if' statement for a works bonus scheme and
struggling a bit. What I'm trying to achieve is the following:
If points>60 then return £2.5 for any points over 60. If less then 60
return 0. Then if points are => 150 then return £3.10 for any points over
150. So far I have:
=IF(E80>60,2.5*(E80-60),IF(E80>=150,3.1*(E80-150),0)) but it goes wrong over
Can anyone help me correct this.
go the other way
donaldb@28...calculate the date & time
Can Anyone help,
I want to be able to calculate easily the difference btwn two date and times,
start - 21-Jan-2004 07:30
finish - 22-Jan-2004 06:30
the answer I want to see is 23 hours.
If the times are less than 24 hrs I don't have an issue, but if the shutdown
goes over 24 hours I have some difficulties calculating. In reality if I only
enter the start and finish times I really want to see the actual durations.
Start - 07:30
Finish - 06:30
If the shut finishes on the next day at 06:30 it should calculate out at
It very much depends on whether they will ...How to convert two dates in to HH:MM:SS
I am trying to calculates the two dates cells diffrence int
hour:minutes:seconds. could some help me in finding a solution i
eg. Cell A1 = Jan-01-2004 00:00 Cell B1 = Jan-02-2004 23:30
Cell C1 = 47:30 looking for a result like 47 hours 3
sashaikh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1555
View this thread: http://www.excelforum.com/showthread.php?threadid=27117
Format C1 with Format/Cells/...Can you use an if then statement to select bold values from a list?
I have a column of data and some of the values are in bold to stand out. Can I use an if then statment to look at a cell, determine if the value is in bold font and, if it is, do an action?
example: (in cell B1)
EggHeadCafe - Software Developer Portal of Choice
Useful Google Search Tips and Tricks
One way is to use your own UserDefinedFunction that can check the formatting.
Function IsBold(rng As Range) As B...extract the month of a date
I want to extract the month of a date. for example I have dat
"6/5/2005" which is in "mm/dd/yyyy" format. i want to get the mont
part of the date as "Jun". Similarly for the date "8/12/2005" a
Presently I am doing this with the help of VLOOKUP approach with
table having these values.
As i am having a huge volume of data to work with a quicker and easie
formula can be a great help.
gireesh's Profile: http://www.excelforum.com/member.php?a...Can I import my business banking statement online into excel?
I have only used excel to do a rudamentary personal budget, but I have a
business that uses online banking...can I import data from my online bank
statement into an excel spreadsheet everymonth and avoid having to purchase
Yes, I believe you can, just check the formatting of each cell the first
time you import the data. After that you can use all Excel functionality,
"one crying in the wilderness" <one crying in the
firstname.lastname@example.org> wrote in message
>I hav...restricting invoice dates
Does the GP software have an option to setup restrictions on invoice dates
both in Accounts Payable and Accounts Receivable. For example if the current
period is May of 2006 you can only enter invoices dates of April and May of
There is a free 'customization' you can get from GP Support called Document
Date Verify. With this customization installed and with the fiscal periods
for all months except April and May closed for the Purchasing and Sales
series (Tools > Setup > Company > Fiscal Periods) users trying to enter
invoices with other dates will ...condition format based on lapsed dates
I want to format a group of cell based on days old. The days is based on the
"value" in cell I8 =Now() and cell J10 (j11,j12...) on is the submission
date. Therefore number of days that has passed is in cell I10 (i11,i12...)
=$I$8-J10" in the cell, this is the number of days that has lapsed.
So if 90 days has passed I want to grey out cells k5,l5,m5, and n5. I am
unable to get this to work, what do I need to do to make this work?
I am assuming that the value is not showing correctly in the formula portion
of the conditional format: if cell(I10)>= 90 then [grey out cell...Math with Dates?
I'm sure this is easy but I have some dates that were entered in Exce
and it automatically filled in the current year but the dates wer
actually from last year. How can I make a command to basically tak
the date and remove 1 year from it?
Possibly a second column that can do a function to do that to get th
new dates and then I can copy it over the old ones or whatever would b
Message posted from http://www.ExcelForum.com
How about find 2004/replace 2003 for the selected area
"kelemvor >" <<kelem...Date and time in a backup file name
I have the small T-SQL script below to backup my databases:
Here is a snippet:
BACKUP DATABASE [model] TO DISK =
WITH NOFORMAT, NOINIT, NAME = N'model_backup_2010_07_05_212931', SKIP,
REWIND, NOUNLOAD, STATS = 10
how to get timestamp automatically generated on execution ?
Thanks in advance
> how to get timestamp automatically generated on execution ?
You can build the desired filename in a variable for use in the BACKUP
DECLARE @Bac...Formatting Dates in a sheet imported from ACCESS
I have imported a report from ACCESS 97 into EXCEL 97 and this worked fine.
However, when I tried to format the dates in EXCEL, nothing happens. I can
format other cells, just the existing date cells won't change unless I
retype the information. Seems almost like the format is locked??
Please advise how I can format these cells.
The dates are being treated as text. There are some techniques here:
that you could try, on a copy of your workbook, to convert the text
dates to real dates.
Orlando Omizzolo wrote:
>...Text Form Date
In my Word 2007 document, I have a text form with Type equal to 'Date'. Is
there a way I can set the default date to be the current date? (I don't want
to set the Type to 'Current date' because then the user can't change it.)
This could be done with an "Entry" macro assigned to the form field in
question. What follows is a simple version of such a macro:
If Trim$(ActiveDocument.FormFields("Text1") _
..Result) = "" Then
..Result = Format$...getting data from date range
hi here is what I need. I need to run a query that picks the data i
specify from this week's Monday back to and including the previous
week's Monday. i need it to run this way in case the query is not run
on Monday. Example:
if I were to run it today, i would get data from 3/15/2010 back to and
including 3/8/2010. If i run it tomorrow, I get the same data. Any
suggestions would be helpful. Thanks
On Thu, 18 Mar 2010 08:53:06 -0700 (PDT), pat67 <email@example.com> wrote:
>hi here is what I need. I need to run a query that picks the data i
>specify from this w...HELP with an INSERT INTO statement
I received the response below for a previous question. I understand the
logic behind the reply, and I am sure it will work. I can't, however, figure
out what I should be substituting for '2345' in the "SELECT '2345' AS" part
of the solution below?
I'm getting very close to running out of time and would greatly appreciate
> Use an append query. Probably you'll need to write VBA code to
> assemble and execute the necessary SQL statement, which could look
> something like this:
...Calculating dates to include Saturdays but not Sundays
I am trying to help a co-worker create a formula to count business days for
the mortgage department.
They need to be able calculate the date three days from a given date. The
three days should include Saturdays but not any federal holidays (US) or
They also need to do a second calculation of 7 days from a given date that
does include holidays and Saturdays. The 7 day calculation does not need to
Is there any way this can be done?
Thank you for your response. I think that this will help my co-worker.
"מי...How do I convert US date with 12hr format to European date 24hr
I am using Excel. I have a mix of 24hr and 12hr AM/PM format pulling from a
database. I want to reformat all dates with time to the 24hr clock so that I
can then have 1 format to manipulate
If the input data are formatted so that Excel recognises them as times, then
it doesn't matter whether they are 12 hour or 24 hour format. If you want
to change them all to one format, use Format/ Cells/ Number/ Time, and
choose to suit your preference.
You may have problems if your data are treated as text because they have
spare spaces or non-breaking spaces. In that case you'd ha...How to handle blank date and time?
I use MFC Visual C++ 4.2
I am developing a database application using MS Access 2000.
The problem I am dealing with is how to handle if the user does not enter
the date. There is a condition where they can do that for Finish Process Date
/ Finish Process Time.
I make the Date and Time as separate fields, both in the database and the
application. On my application I use the COleDateTime data type.
I have to save the blank Date and the blank Time, and I also have to acquire
them back in order to display the data using the CListCtrl.
What I am confusing is how to make the query?
Is it...Project Billings: Received to Date
I am printing an Open Contracts report from smartlist to go over my open
projects. My Received to Date for the contracts never seem to be correct.
I have run a utilities on Project for billings etc and aging but cannot seem
to get the numbers up to date. What can I run to get the numbers up to
date with the correct cash received to date?
I tested this on my system here using your setup information however I did
not receive the same results that you are experiencing. When I ent...Last saved date #2
I would like a spreadsheet to show its last saved date in a cell, which
updates automatically when the spreadsheet is changed and saved - well,
saved, as I wouldn't save it if there were no changes. I thought there was
a function that did this but I can't find anything. Using Excel in Office XP
Bryan, you could use something link this, put it in the thisworkbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
'If you save the file the date will be placed in cell A1of Sheet1
Sheets("Sheet1").Range("...pivot tables by date range
I've created a pivot table that shows categories of expenditures in the row
and dates in the column. The data is the amount spent.
I want to show only expenditures from 2004 (it's tax time!). Is there a
simple way to do this (rather than unchecking all the boxes from 1992, 1993,
1994, 2005, etc) .
Move the "year" field into the "Page" area in the Wizard. It'll default
to ALL so you'll need to select the year.
...weekday and date series fill
I'd like to create an academic lesson plan book with weekday and date as the
column heading. Is there a simple way to combine the weekday plus the date
(for example, Tuesday, July 29) and have it generate 36 weeks of dates? I
know how to do this seperately (weekday or date), and have tried a few
different simple things, but nothing seems to be working for me. Thanks for
Here is what I did to get "Wednesday Jul 30" in a cell.
Select the start cell, say A1.
Select Format,Cells and "Custom"
In the widow on the right side under "Type:&...If statements and Dates
I need to flag milestone yrs of service for our employees.
I was able to download the hiredates from my crappy system but the date
appears as mm/dd/yyyy. What formula can I use to flag those who have or
will have completed their 10th, 15th, 20th, 25th, 30th (etc...) year of
service between Sept 2005 and Aug 2006.
Your assistance is greatly appreciated.
(remove nothere from email address if mailing direct)
"Sue" <Sue@discussions.microsoft.com> wrote in message
news:08B55C0D-AB...Date format pre-1900.
I have a table of immigration ships that have arrived in Australian shores.
Some....okay most.....of the ships arrived before 1900.
I was trying to sort by the arrival date, which is in the format of
DD MMM YYYY, but that didn't work, as there were some 1900 dates.
Tried to re-style the column to DD/MM/YYYY, but the pre-1900 dates
don't change to the new style.
Any suggestions on how to utilise the pre-1900 dates, so that Excel can
Cheers in advance.
You could use the add-in at http://j-walk.com/ss/excel/files/xdate.htm