Today's Date

[Guest]

Is there a way to get excel to output the numerical value of today’s day of
the month? For example, today is 12/17/04. I want the value in the cell to
be “17†today. When I open the file tomorrow, I want it to automatically
change to “18â€. I have figured out how to get excel to DISPLAY these numbers
(=today(), then change formatting to custom date, dd). However, the true
value of the cell is still the date code (in this example, 38338). So if I
multiply that cell by 1, the result is 38338, not 17. I want 17. Can this
be done?

To give a bit more detail on why I want to do this – I want to be able to
calculate the percentage of this month that is complete. So I want to have a
ratio that equals today’s date divided by the number of days in the month.
Right now, I manually update a cell to today’s day every time I open the
spreadsheet but I want excel to update automatically.

Thanks!

 

[Guest]

One way

=DAY(TODAY())

Regards,

Peo Sjoblom

 

[Guest]

Ok, I figured this out actually. =day(today()) Pretty easy!

But now I guess the question is, can excel figure out how many days are in
the current month? Can I have excel output "31" since it knows today's date
is 12/17/04 and there are 31 days in Dec?

 

[Sandy Mann]

Jake,

Try
=DAY(TODAY())
formatted as General

HTH

Sandy

 

[Paulw2k]

=DAY(TODAY())

 

[Guest]

One way

=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))


Regards,

Peo Sjoblom

 

[Guest]

Or this little gem from Daniel Maher

=32-DAY(TODAY()-DAY(TODAY())+32)

Regards,

Peo Sjoblom

 

[Ron Rosenfeld]

Is there a way to get excel to output the numerical value of today’s day of
the month? For example, today is 12/17/04. I want the value in the cell to
be “17” today. When I open the file tomorrow, I want it to automatically
change to “18”. I have figured out how to get excel to DISPLAY these numbers
(=today(), then change formatting to custom date, dd). However, the true
value of the cell is still the date code (in this example, 38338). So if I
multiply that cell by 1, the result is 38338, not 17. I want 17. Can this
be done?
=DAY(TODAY())


To give a bit more detail on why I want to do this – I want to be able to
calculate the percentage of this month that is complete. So I want to have a
ratio that equals today’s date divided by the number of days in the month.
Right now, I manually update a cell to today’s day every time I open the
spreadsheet but I want excel to update automatically.

=DAY(TODAY())/DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

=17/31

=0.548387097




--ron

 

[Guest]

Thanks, that one is brilliant...

I've got my answer, but I'm still a bit frustrated because I thought I found
a way that should work but it doesn't.

Why doesn't this work?

=DAY(EOMONTH(TODAY(),0))

If I do =EOMONTH(TODAY(),0), I get the last day of current month. Then if I
reference that from a different cell and do =Day(previous cell), I get the
answer I want. But for whatever reason, when I try to do it all in one cell
(=DAY(EOMONTH(TODAY(),0))), I get 01/31/1900. Any idea what's going on???

 

[Guest]

It's the same answer, if you put 31 in a cell and format it as a date you
will get

01/31/1900

it's because excel dates start with 1900 and increments by 1 each day, if
you take today's date and format it as general you will get 38338 (days since
January 0 1900)
So just format the result as general Because you are using a date function
excel try to be helpful and format it as date.. Note that eomonth is part of
the ATP and many users might not have it installed


Regards,

Peo Sjoblom