Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Today's Date
Is there a way to get excel to output the numerical value of todays 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 todays date divided by the number of days in the month. Right now, I manually update a cell to todays day every time I open the spreadsheet but I want excel to update automatically. Thanks! |
#2
|
|||
|
|||
One way
=DAY(TODAY()) Regards, Peo Sjoblom "Jake" wrote: Is there a way to get excel to output the numerical value of todays 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 todays date divided by the number of days in the month. Right now, I manually update a cell to todays day every time I open the spreadsheet but I want excel to update automatically. Thanks! |
#3
|
|||
|
|||
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? "Jake" wrote: Is there a way to get excel to output the numerical value of todays 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 todays date divided by the number of days in the month. Right now, I manually update a cell to todays day every time I open the spreadsheet but I want excel to update automatically. Thanks! |
#4
|
|||
|
|||
Jake,
Try =DAY(TODAY()) formatted as General HTH Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "Jake" wrote in message ... 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! |
#5
|
|||
|
|||
=DAY(TODAY())
"Jake" wrote in message ... 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! |
#6
|
|||
|
|||
One way
=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)) Regards, Peo Sjoblom "Jake" wrote: 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? "Jake" wrote: Is there a way to get excel to output the numerical value of todays 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 todays date divided by the number of days in the month. Right now, I manually update a cell to todays day every time I open the spreadsheet but I want excel to update automatically. Thanks! |
#7
|
|||
|
|||
Or this little gem from Daniel Maher
=32-DAY(TODAY()-DAY(TODAY())+32) Regards, Peo Sjoblom "Peo Sjoblom" wrote: One way =DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)) Regards, Peo Sjoblom "Jake" wrote: 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? "Jake" wrote: Is there a way to get excel to output the numerical value of todays 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 todays date divided by the number of days in the month. Right now, I manually update a cell to todays day every time I open the spreadsheet but I want excel to update automatically. Thanks! |
#8
|
|||
|
|||
On Fri, 17 Dec 2004 13:03:06 -0800, "Jake"
wrote: Is there a way to get excel to output the numerical value of todays 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 todays date divided by the number of days in the month. Right now, I manually update a cell to todays 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 |
#9
|
|||
|
|||
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??? "Peo Sjoblom" wrote: Or this little gem from Daniel Maher =32-DAY(TODAY()-DAY(TODAY())+32) Regards, Peo Sjoblom "Peo Sjoblom" wrote: One way =DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)) Regards, Peo Sjoblom "Jake" wrote: 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? "Jake" wrote: Is there a way to get excel to output the numerical value of todays 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 todays date divided by the number of days in the month. Right now, I manually update a cell to todays day every time I open the spreadsheet but I want excel to update automatically. Thanks! |
#10
|
|||
|
|||
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 "Jake" wrote: 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??? "Peo Sjoblom" wrote: Or this little gem from Daniel Maher =32-DAY(TODAY()-DAY(TODAY())+32) Regards, Peo Sjoblom "Peo Sjoblom" wrote: One way =DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)) Regards, Peo Sjoblom "Jake" wrote: 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? "Jake" wrote: Is there a way to get excel to output the numerical value of todays 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 todays date divided by the number of days in the month. Right now, I manually update a cell to todays day every time I open the spreadsheet but I want excel to update automatically. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
Date Formula | Excel Discussion (Misc queries) | |||
Date formula | Excel Discussion (Misc queries) | |||
Problem with Date format from VBA code | Excel Discussion (Misc queries) | |||
How do I convert a Julian date into a regular date? | Excel Discussion (Misc queries) |