Excel - Convert date to number

Asked By @Homeonthecouch on 19-Apr-08 04:23 AM
I am wanting to convert date using the today() function to the date only for a sum.
e.g. A1 =Today()
B1= 300
C1= B1/A1

The cell that divides would be where the date conversion is made.

Hope that makes sense

Any help is appreciated

Andrew




Niek Otten replied on 19-Apr-08 04:36 AM
Hi Andrew,

The date is a number already. Just format A1 as General and you'll see the date serial number.
What do you want to achieve? If you need the date number without the year:
=A1-DATE(YEAR(A1),1,1)+1, Format as General or number (Excel will format it automatically as date, which is not what you need)

If you just want to show the date without a year, format it Custom as mm dd or something similar
--
Kind regards,

Niek Otten
Microsoft MVP - Excel
Rick Rothstein \(MVP - VB\) replied on 19-Apr-08 05:26 AM
I think this should do what you want...

C1:     =B1/N(A1)

Rick
martin_fishloc replied on 19-Apr-08 11:09 AM
Hi Couchie,

Today() returns a number that is x number of days from 1/1/1900 and then
format it as a date. If you set it as general format you will see the number
for today.

So if you have today() on the April 19th, 2008 the number is 39557.

So I'm not sure what you want with 300/39557 or a similar number.

Therefore I'm not sure what you wamt to do.

Please explain further.
--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Bangkok, Thailand
Please do not forget to rate this reply.
@Homeonthecouch replied on 19-Apr-08 12:20 PM
Ah is see I haven't explained this clearly.
If today = 10/4/2008 I want the value of A1 to just equal the day 19.
Then the sum in cell C1 would be 300/19 or tomorrow it would be 300/20

Hope this explains it a little clearer.

Andrew

I am wanting to convert date using the today() function to the date only for a sum.
e.g. A1 =Today()
B1= 300
C1= B1/A1

The cell that divides would be where the date conversion is made.

Hope that makes sense

Any help is appreciated

Andrew
Rick Rothstein \(MVP - VB\) replied on 19-Apr-08 12:46 PM
I am assuming your saying if 10/4/2008 then the day is 19 was a mistyping of
the number 10. Try putting this in C1...

C1:      =B1/DAY(A1)

Rick