PDA

View Full Version : Date arithmetic in Excel

Tinakarori
30-08-2004, 05:19 PM
The "Date" function in Excel converts a date formatted as a date with one of the standard Excel date formats to a serial number. This is very useful for performing a whole range of date arithmetic.

The function syntax is =DATE(year,month,day), ie it will only readily work with dates entered and formatted in the peculiar US notation only.

Can anyone suggest an automated function or method to derive the date serial number for a date formatted in the normal NZ (and most of the rest of the civilized world) format, eg 30/08/04 or 30 August 2004?

andrew93
30-08-2004, 06:01 PM
Hi Tinakouri

I'm not quite sure what you are after but here is a way of getting the date serial number without using the date function :

If you format the cell containing the date as "number" (Format, Cells, Number, Number, zero decimal places, Ok) then you get the date serial number expressed as the number of days after January 1st, 1900 (with Jan 1st 1900 being 1 and 30 August 2004 being 38,229).

If this is not what you are after and you literally want to use the date function but referenced to an NZ format date then let us know and either parry or rugila may have an answer for you.

HTH

Greg S
30-08-2004, 06:57 PM
Try =TODAY()

Greg S
30-08-2004, 06:58 PM
> Try =TODAY()

ps I'm using Excel 2003 - not sure if that makes any difference

andrew93
30-08-2004, 08:02 PM
Hi Tinakarori, sorry for getting your name wrong on my previous post.

I think I have the answer if you wish to use the date function on a NZ format date. Assuming the date value is stored in cell A1, try this :
=DATE(YEAR(A1),MONTH(A1),DAY(A1))

This formula should work for any date format (NZ, US etc.) but I still think it is easier to use the cell formatting options to get the date serial number.

HTH, Andrew

parry
30-08-2004, 08:57 PM
Hi, as Andrew has explained the function requires 3 arguments, it just happens that the order is YYYY MM DD because Excel is created in the US. So whether it was formatted as DD/MM/YYYY you still have to use 3 functions within Date (Year,Month,Day) to get the values from a cell with a date value.

You dont actually need to convert the value to a serial in most circumstances as you can just use different formatting where required.
eg A1 has 15/8/2004, B1 has 12/8/2004 and the formula in C1 has
=A1-B1 which will return 03/01/1900 if formatted as a date or 3 if formatted as a number.

An alternative to the Date function is DateValue which accepts a text argument, so your target cell must be changed to text (use the Text function). Heres an example....
=DATEVALUE(TEXT(A1,"DD/MM/YY"))

hth

Russell D
30-08-2004, 09:05 PM
Working in reverse -
The date serial for 30 August 2004 is 38229.
Enter this number in a few cells, then format one cell as Custom dd/mm/yy which will display 30/08/04, then custom format another cell as mm/dd/yy which will display 08/30/04.
The serial number is constant, but there are many ways to display it on screen - more examples - Custom format another as dddd, another as yyyy.
As others have said - if you want the serial number of a cell containing any form of displayed date, format the cell as a number.

BTW - if you enter =DATE(04,08,30) in a cell you may get different results than expected!