Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
Hi All,
If I enter, for example, a birth date in column A1 and the below formula in another column, I understand the result will be someone's age in years, months, and days. My question is: Tomorrow, when I open my file, will my result show one more day added to the "days" number? Thanks for your time. =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" -- sherry |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
Well, kinda...
DATEDIF() uses the length of the first month to determine month lengths, so that leads to some bizarre results: Assume A1 = 1/31/2007 then some results will look like: 2/27/2009 2 years, 0 months, 27 days 2/28/2009 2 years, 0 months, 28 days 3/1/2009 2 years, 1 months, -2 days 3/2/2009 2 years, 1 months, -1 days 3/3/2009 2 years, 1 months, 0 days 3/4/2009 2 years, 1 months, 1 days 3/5/2009 2 years, 1 months, 2 days likewise 4/29/2009 2 years, 2 months, 29 days 4/30/2009 2 years, 2 months, 30 days 5/1/2009 2 years, 3 months, 0 days 5/2/2009 2 years, 3 months, 1 days 5/3/2009 2 years, 3 months, 2 days In article , sherry wrote: Hi All, If I enter, for example, a birth date in column A1 and the below formula in another column, I understand the result will be someone's age in years, months, and days. My question is: Tomorrow, when I open my file, will my result show one more day added to the "days" number? Thanks for your time. =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
hi
yes it will. but i would substitute Today() for Now() in the formula since now() incluces time also. the datediff() formula will strrip the time off but using today() would just be less clutter excel has to trip over. my thoughts. Regards FSt1 "sherry" wrote: Hi All, If I enter, for example, a birth date in column A1 and the below formula in another column, I understand the result will be someone's age in years, months, and days. My question is: Tomorrow, when I open my file, will my result show one more day added to the "days" number? Thanks for your time. =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" -- sherry |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
Good point - I'll change it right now. thanks!!
-- sherry "FSt1" wrote: hi yes it will. but i would substitute Today() for Now() in the formula since now() incluces time also. the datediff() formula will strrip the time off but using today() would just be less clutter excel has to trip over. my thoughts. Regards FSt1 "sherry" wrote: Hi All, If I enter, for example, a birth date in column A1 and the below formula in another column, I understand the result will be someone's age in years, months, and days. My question is: Tomorrow, when I open my file, will my result show one more day added to the "days" number? Thanks for your time. =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" -- sherry |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
So whatever month is in the A1 cell is telling my whole DATEIF() formula how
many days to use to count my days in a month?? It seems to me that it would change each time I pasted the formula in a different cell next to a different date. I guess I'm missing something. Can you offer advice on a better formula. Any help is greatly appreciated. However, right before I hit "post", I just checked my results. None were wrong. I put in your dates and did not get the results you got. I'm using Excel 2007. Maybe you're speaking of a different version and this one actually works right!! :) -- sherry "JE McGimpsey" wrote: Well, kinda... DATEDIF() uses the length of the first month to determine month lengths, so that leads to some bizarre results: Assume A1 = 1/31/2007 then some results will look like: 2/27/2009 2 years, 0 months, 27 days 2/28/2009 2 years, 0 months, 28 days 3/1/2009 2 years, 1 months, -2 days 3/2/2009 2 years, 1 months, -1 days 3/3/2009 2 years, 1 months, 0 days 3/4/2009 2 years, 1 months, 1 days 3/5/2009 2 years, 1 months, 2 days likewise 4/29/2009 2 years, 2 months, 29 days 4/30/2009 2 years, 2 months, 30 days 5/1/2009 2 years, 3 months, 0 days 5/2/2009 2 years, 3 months, 1 days 5/3/2009 2 years, 3 months, 2 days In article , sherry wrote: Hi All, If I enter, for example, a birth date in column A1 and the below formula in another column, I understand the result will be someone's age in years, months, and days. My question is: Tomorrow, when I open my file, will my result show one more day added to the "days" number? Thanks for your time. =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
Ah, it appears that XL07 has changed DATEDIF(), as I get expected
results for the examples I gave. However, as I wrote before, it is NOT possible to develop a *general and consistent* formula. It's an inherent problem in the fuzzy definition of "months". With XL07, MS has (apparently) decided to shift the point where the error occurs. Why they'd do that, I don't know, But that doesn't make it right. If anything, in many ways, it's WORSE. In XL07, set 1/31/2007 as your base date, then: 1/30/2009 1 years, 11 months, 30 days 1/31/2009 2 years, 0 months, 0 days 2/1/2009 2 years, 0 months, -2 days 2/2/2009 2 years, 0 months, -1 days 2/3/2009 2 years, 0 months, 0 days 2/4/2009 2 years, 0 months, 1 days So now by advancing one day (1/31 - 2/1) the result actually appears to lose 2 days! Or, use 2/28/2007 as your base date: 2/27/2009 1 years, 11 months, 27 days 2/28/2009 2 years, 0 months, 0 days 3/1/2009 2 years, 0 months, 4 days 3/2/2009 2 years, 0 months, 5 days Note the jump from 2Y 0M 0D, to 2Y 0M 4D when advancing only one actual day... If you can live with that, that's OK, of course. In article , sherry wrote: However, right before I hit "post", I just checked my results. None were wrong. I put in your dates and did not get the results you got. I'm using Excel 2007. Maybe you're speaking of a different version and this one actually works right!! :) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
First of all, you don't need to actually "type in" a base date anywhere. You
just use the date you want to use. In my case, they are birth dates. Type in your birthdate in A1. Type in the formula in B1 and it will show you how old you are in years, months and days. That's all I need to show. Secondly, I will never have the problem you're describing (for this task anyway) because the examples that are showing errors are future dates. Once again, I am using birth dates. You need to be born before I can enter your date so I will not be putting in future dates! :) -- sherry "sherry" wrote: Hi All, If I enter, for example, a birth date in column A1 and the below formula in another column, I understand the result will be someone's age in years, months, and days. My question is: Tomorrow, when I open my file, will my result show one more day added to the "days" number? Thanks for your time. =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" -- sherry |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
sorry - posted to the wrong member: First of all, you don't need to actually "type in" a base date anywhere. You just use the date you want to use. In my case, they are birth dates. Type in your birthdate in A1. Type in the formula in B1 and it will show you how old you are in years, months and days. That's all I need to show. Secondly, I will never have the problem you're describing (for this task anyway) because the examples that are showing errors are future dates. Once again, I am using birth dates. You need to be born before I can enter your date so I will not be putting in future dates! :) -- sherry "JE McGimpsey" wrote: Ah, it appears that XL07 has changed DATEDIF(), as I get expected results for the examples I gave. However, as I wrote before, it is NOT possible to develop a *general and consistent* formula. It's an inherent problem in the fuzzy definition of "months". With XL07, MS has (apparently) decided to shift the point where the error occurs. Why they'd do that, I don't know, But that doesn't make it right. If anything, in many ways, it's WORSE. In XL07, set 1/31/2007 as your base date, then: 1/30/2009 1 years, 11 months, 30 days 1/31/2009 2 years, 0 months, 0 days 2/1/2009 2 years, 0 months, -2 days 2/2/2009 2 years, 0 months, -1 days 2/3/2009 2 years, 0 months, 0 days 2/4/2009 2 years, 0 months, 1 days So now by advancing one day (1/31 - 2/1) the result actually appears to lose 2 days! Or, use 2/28/2007 as your base date: 2/27/2009 1 years, 11 months, 27 days 2/28/2009 2 years, 0 months, 0 days 3/1/2009 2 years, 0 months, 4 days 3/2/2009 2 years, 0 months, 5 days Note the jump from 2Y 0M 0D, to 2Y 0M 4D when advancing only one actual day... If you can live with that, that's OK, of course. In article , sherry wrote: However, right before I hit "post", I just checked my results. None were wrong. I put in your dates and did not get the results you got. I'm using Excel 2007. Maybe you're speaking of a different version and this one actually works right!! :) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
Again, if you're OK with that, that's fine.
But, your argument about birth dates vs. future dates doesn't make any sense to me whatsoever! Let's say you have an already-born Ms. Jones recorded as having come into the world on 31 January 1965. Today (17 January 2009), when you check her age, you'll find that XL (I'm assuming XL07) believes Ms. Jones to be 43 years, 11 months, 17 days old. And most people would probably agree. If today's the last day you're going to use that spreadsheet, you're good to go and I've wasted my time with what comes next. However, let's say you want to check your spreadsheet again on Monday, 2 February 2009. When you fire it up, you'll find that XL thinks that Ms. Jones is now 44 years, 0 months, -1 days old. Hmm... a day less than 44 years? For MOST people, that doesn't make much sense - how can one be less than 44 years old two days after one turned 44? When, a couple months later, you fire up XL on Tuesday, 31 March 2009, XL will tell you that Ms. Jones is now the ripe old age of 44 years, 2 months, 0 days old. Again, most people would probably agree. But when you check again the next day (1 April), XL will tell you that Ms. Jones is STILL 44 years, 2 months, 0 days old. April Fools! Once again, for MOST people, this behavior is not desirable. For MOST people, the age of someone shouldn't be reported this way (but exactly *how* it should be reported varies from situation to situation). I'm only trying to point out a potential problem. Since there is NO way to consistently and generally solve this problem, you're either going to have to live with it (which is FINE), or you're going to have to specify what you expect to happen around month boundaries and craft a consistent (but not general) formula. In article , sherry wrote: Secondly, I will never have the problem you're describing (for this task anyway) because the examples that are showing errors are future dates. Once again, I am using birth dates. You need to be born before I can enter your date so I will not be putting in future dates! :) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
Thank you so much for all the time you took responding to my issue. Have a
great day! :) -- sherry "sherry" wrote: Hi All, If I enter, for example, a birth date in column A1 and the below formula in another column, I understand the result will be someone's age in years, months, and days. My question is: Tomorrow, when I open my file, will my result show one more day added to the "days" number? Thanks for your time. =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" -- sherry |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Upon Opening based on DATEIF NOW()
Thank you so much for all the time you took responding to my issue. Have a
great day! :) -- sherry "JE McGimpsey" wrote: Again, if you're OK with that, that's fine. But, your argument about birth dates vs. future dates doesn't make any sense to me whatsoever! Let's say you have an already-born Ms. Jones recorded as having come into the world on 31 January 1965. Today (17 January 2009), when you check her age, you'll find that XL (I'm assuming XL07) believes Ms. Jones to be 43 years, 11 months, 17 days old. And most people would probably agree. If today's the last day you're going to use that spreadsheet, you're good to go and I've wasted my time with what comes next. However, let's say you want to check your spreadsheet again on Monday, 2 February 2009. When you fire it up, you'll find that XL thinks that Ms. Jones is now 44 years, 0 months, -1 days old. Hmm... a day less than 44 years? For MOST people, that doesn't make much sense - how can one be less than 44 years old two days after one turned 44? When, a couple months later, you fire up XL on Tuesday, 31 March 2009, XL will tell you that Ms. Jones is now the ripe old age of 44 years, 2 months, 0 days old. Again, most people would probably agree. But when you check again the next day (1 April), XL will tell you that Ms. Jones is STILL 44 years, 2 months, 0 days old. April Fools! Once again, for MOST people, this behavior is not desirable. For MOST people, the age of someone shouldn't be reported this way (but exactly *how* it should be reported varies from situation to situation). I'm only trying to point out a potential problem. Since there is NO way to consistently and generally solve this problem, you're either going to have to live with it (which is FINE), or you're going to have to specify what you expect to happen around month boundaries and craft a consistent (but not general) formula. In article , sherry wrote: Secondly, I will never have the problem you're describing (for this task anyway) because the examples that are showing errors are future dates. Once again, I am using birth dates. You need to be born before I can enter your date so I will not be putting in future dates! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DATEIF function help pleeeeeease | Excel Worksheet Functions | |||
IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
DATEIF Name error | Excel Worksheet Functions | |||
DATEIF and IF statements | Excel Worksheet Functions | |||
CALCULATING DATES AFTER DATEIF | Excel Discussion (Misc queries) |