Calculating Date Number

[Pete Hawkins]

Hi,

Trying to fathom out how to enter a Date in one cell and for that date to be
used in another cell to calculate a serial number.

What do I mean?

Worksheet user enters a date (29/4/2004). Elsewhere in the spreadsheet a
formula picks up that date, turns it into a serial number to calculate the
number of days beyond January the first it is.

I¹ve tried using the formula DATEVALUE with a cell reference B1 but this
returns a #VALUE error

Your help appreciated.

Cheers,


Pete

 

[Frank Kabel]

Hi
you can simply use
=A1
and format this resulting cell as number. But if you just want to
subtract two dates use
=B1-A1
and format as number

 

[Pete Hawkins]

Great,

It worked.

Thanks


Pete

 

[Myrna Larson]

The reason that the other solutions work is that there's no need to "turn the
date into a number". Excel did that automatically when the user typed the
date. If you type 29/4/2004 in A1, then clear the formatting, you'll see what
I mean.

You get the error with DATEVALUE because the argument for that function must
be TEXT that *looks like* a date to you and me, but hasn't been interpreted as
a date by Excel and thus automatically converted to a number.

The formula to give the number of days since Jan 1 for the data in A1 is

=A1-DATE(YEAR(A1),1,1)

If you want the day of the year, change the final 1 above to a 0.