| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DATEDIF
I used the following formula to calculate the difference between two dates:
=DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, "&DATEDIF(A2,B2,"md") & " days" 01/01/2006 28/02/2006 0 years, 1 months, 27 days 01/01/2006 31/01/2006 0 years, 0 months, 30 days Which is ok but I would want the result for the first example to be 2 months and for the second example 1 month. Should I use another formula? Thanks in advance Deborah |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEDIF
Deborah wrote:
I used the following formula to calculate the difference between two dates: =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, "&DATEDIF(A2,B2,"md") & " days" 01/01/2006 28/02/2006 0 years, 1 months, 27 days 01/01/2006 31/01/2006 0 years, 0 months, 30 days Which is ok but I would want the result for the first example to be 2 months and for the second example 1 month. Should I use another formula? Thanks in advance Deborah Hi Deborah, try with this modified formula: =DATEDIF(A2,B2+1,"y") & " years, " & DATEDIF(A2,B2+1,"ym") & " months, "&DATEDIF(A2,B2+1,"md") & " days" -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEDIF
It seems to work.
Many thanks. Deborah from Italy "Franz Verga" wrote: Deborah wrote: I used the following formula to calculate the difference between two dates: =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, "&DATEDIF(A2,B2,"md") & " days" 01/01/2006 28/02/2006 0 years, 1 months, 27 days 01/01/2006 31/01/2006 0 years, 0 months, 30 days Which is ok but I would want the result for the first example to be 2 months and for the second example 1 month. Should I use another formula? Thanks in advance Deborah Hi Deborah, try with this modified formula: =DATEDIF(A2,B2+1,"y") & " years, " & DATEDIF(A2,B2+1,"ym") & " months, "&DATEDIF(A2,B2+1,"md") & " days" -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEDIF
Deborah wrote:
It seems to work. Many thanks. You're welcome. Deborah from Italy There's also the italian speaking newsgroup: microsoft.public.it.office.excel -- Glad I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEDIF
Hi Franz, I tried your modified formula but it does not work. For example
14.02.1980 01.04.1995 15 years, 1 month, 19 days whereas the actual difference is 15 years, 1 month, 17 days including both monthdays. The actual and modified formula both give incorrect results. I think excel is not able to work out the days of February days, specially for the years divisible by 4, 100, 400 and 1600. Can anybody help me. Thanks, Javaid from Pakistan. "Franz Verga" wrote: Deborah wrote: I used the following formula to calculate the difference between two dates: =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, "&DATEDIF(A2,B2,"md") & " days" 01/01/2006 28/02/2006 0 years, 1 months, 27 days 01/01/2006 31/01/2006 0 years, 0 months, 30 days Which is ok but I would want the result for the first example to be 2 months and for the second example 1 month. Should I use another formula? Thanks in advance Deborah Hi Deborah, try with this modified formula: =DATEDIF(A2,B2+1,"y") & " years, " & DATEDIF(A2,B2+1,"ym") & " months, "&DATEDIF(A2,B2+1,"md") & " days" -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEDIF
Hi Franz, I tried your modified formula but it does not work. For example
14.02.1980 01.04.1995 15 years, 1 month, 19 days whereas the actual difference is 15 years, 1 month, 17 days including both monthdays. The actual and modified formula both give incorrect results. I think excel is not able to work out the days of February days, specially for the years divisible by 4, 100, 400 and 1600. Can anybody help me. Thanks, waiting for the reply. Javaid from Pakistan. "Franz Verga" wrote: Deborah wrote: I used the following formula to calculate the difference between two dates: =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, "&DATEDIF(A2,B2,"md") & " days" 01/01/2006 28/02/2006 0 years, 1 months, 27 days 01/01/2006 31/01/2006 0 years, 0 months, 30 days Which is ok but I would want the result for the first example to be 2 months and for the second example 1 month. Should I use another formula? Thanks in advance Deborah Hi Deborah, try with this modified formula: =DATEDIF(A2,B2+1,"y") & " years, " & DATEDIF(A2,B2+1,"ym") & " months, "&DATEDIF(A2,B2+1,"md") & " days" -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEDIF
=DATEDIF(E5,TODAY(),"Y") & " Years "
i have used this formula, but i do not want the word years to show up in the cell, how do i modify it. "Franz Verga" wrote: Deborah wrote: I used the following formula to calculate the difference between two dates: =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, "&DATEDIF(A2,B2,"md") & " days" 01/01/2006 28/02/2006 0 years, 1 months, 27 days 01/01/2006 31/01/2006 0 years, 0 months, 30 days Which is ok but I would want the result for the first example to be 2 months and for the second example 1 month. Should I use another formula? Thanks in advance Deborah Hi Deborah, try with this modified formula: =DATEDIF(A2,B2+1,"y") & " years, " & DATEDIF(A2,B2+1,"ym") & " months, "&DATEDIF(A2,B2+1,"md") & " days" -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEDIF
Thanks Papou, but this does not work, it sends back a date, (such as
2/18/06), but i am looking for the difference in years, (46) "papou" wrote: Hello George =DATEDIF(E5,TODAY(),"Y") HTH Cordially Pascal "George" a écrit dans le message de news: ... =DATEDIF(E5,TODAY(),"Y") & " Years " i have used this formula, but i do not want the word years to show up in the cell, how do i modify it. "Franz Verga" wrote: Deborah wrote: I used the following formula to calculate the difference between two dates: =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, "&DATEDIF(A2,B2,"md") & " days" 01/01/2006 28/02/2006 0 years, 1 months, 27 days 01/01/2006 31/01/2006 0 years, 0 months, 30 days Which is ok but I would want the result for the first example to be 2 months and for the second example 1 month. Should I use another formula? Thanks in advance Deborah Hi Deborah, try with this modified formula: =DATEDIF(A2,B2+1,"y") & " years, " & DATEDIF(A2,B2+1,"ym") & " months, "&DATEDIF(A2,B2+1,"md") & " days" -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEDIF
Format cell as standard number.
HTH Cordially Pascal "George" a écrit dans le message de news: ... Thanks Papou, but this does not work, it sends back a date, (such as 2/18/06), but i am looking for the difference in years, (46) "papou" wrote: Hello George =DATEDIF(E5,TODAY(),"Y") HTH Cordially Pascal "George" a écrit dans le message de news: ... =DATEDIF(E5,TODAY(),"Y") & " Years " i have used this formula, but i do not want the word years to show up in the cell, how do i modify it. "Franz Verga" wrote: Deborah wrote: I used the following formula to calculate the difference between two dates: =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, "&DATEDIF(A2,B2,"md") & " days" 01/01/2006 28/02/2006 0 years, 1 months, 27 days 01/01/2006 31/01/2006 0 years, 0 months, 30 days Which is ok but I would want the result for the first example to be 2 months and for the second example 1 month. Should I use another formula? Thanks in advance Deborah Hi Deborah, try with this modified formula: =DATEDIF(A2,B2+1,"y") & " years, " & DATEDIF(A2,B2+1,"ym") & " months, "&DATEDIF(A2,B2+1,"md") & " days" -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEDIF
I suggest that you check again. To start with, you should format your
DATEDIF output cell as General or Number, not as a date, but if you're getting 2/18/06 that would be equivalent to a number of 2241 if the 2/18/06 were 2/18/1906, or 38766 if it were 2/18/2006, so I don't see how you're getting that from a DATEDIF in years whose finishing point is TODAY(). What value do you have in E5 (firstly as a date, but secondly if you reformat it temporarily to Number or General)? -- David Biddulph "George" wrote in message ... Thanks Papou, but this does not work, it sends back a date, (such as 2/18/06), but i am looking for the difference in years, (46) "papou" wrote: Hello George =DATEDIF(E5,TODAY(),"Y") "George" a écrit dans le message de news: ... =DATEDIF(E5,TODAY(),"Y") & " Years " i have used this formula, but i do not want the word years to show up in the cell, how do i modify it. "Franz Verga" wrote: Deborah wrote: I used the following formula to calculate the difference between two dates: =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, "&DATEDIF(A2,B2,"md") & " days" 01/01/2006 28/02/2006 0 years, 1 months, 27 days 01/01/2006 31/01/2006 0 years, 0 months, 30 days Which is ok but I would want the result for the first example to be 2 months and for the second example 1 month. Should I use another formula? Thanks in advance Deborah Hi Deborah, try with this modified formula: =DATEDIF(A2,B2+1,"y") & " years, " & DATEDIF(A2,B2+1,"ym") & " months, "&DATEDIF(A2,B2+1,"md") & " days" -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DATEDIF
Thank you.
"papou" wrote: Format cell as standard number. HTH Cordially Pascal "George" a écrit dans le message de news: ... Thanks Papou, but this does not work, it sends back a date, (such as 2/18/06), but i am looking for the difference in years, (46) "papou" wrote: Hello George =DATEDIF(E5,TODAY(),"Y") HTH Cordially Pascal "George" a écrit dans le message de news: ... =DATEDIF(E5,TODAY(),"Y") & " Years " i have used this formula, but i do not want the word years to show up in the cell, how do i modify it. "Franz Verga" wrote: Deborah wrote: I used the following formula to calculate the difference between two dates: =DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, "&DATEDIF(A2,B2,"md") & " days" 01/01/2006 28/02/2006 0 years, 1 months, 27 days 01/01/2006 31/01/2006 0 years, 0 months, 30 days Which is ok but I would want the result for the first example to be 2 months and for the second example 1 month. Should I use another formula? Thanks in advance Deborah Hi Deborah, try with this modified formula: =DATEDIF(A2,B2+1,"y") & " years, " & DATEDIF(A2,B2+1,"ym") & " months, "&DATEDIF(A2,B2+1,"md") & " days" -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Datedif function | Excel Discussion (Misc queries) | |||
| DateDif problem | Excel Worksheet Functions | |||
| DateDif Average? Damn DateDif | Excel Worksheet Functions | |||
| DateDif problem | Excel Worksheet Functions | |||
| DATEDIF Simple request | Excel Worksheet Functions | |||
Linear Mode
