Calculating Date Number

  • Thread starter Thread starter Pete Hawkins
  • Start date Start date
P

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
 
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
 
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.
 
Back
Top