| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
Could someone please help with a formula to work out the difference between
two dates and show how many years, months and days there are |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formulas
Hi,
=DATEDIF(B1,B2,"y") whole years between two dates =DATEDIF(B1,B2,"ym") whoe months between two dates excluding years =DATEDIF(B1,B2,"md") whole days between two dates ignoring months where B1 is the start date and B2 is the end date. -- Thanks, Shane Devenshire "kaz" wrote: Could someone please help with a formula to work out the difference between two dates and show how many years, months and days there are |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formulas
thanks heaps - :)
"ShaneDevenshire" wrote: Hi, =DATEDIF(B1,B2,"y") whole years between two dates =DATEDIF(B1,B2,"ym") whoe months between two dates excluding years =DATEDIF(B1,B2,"md") whole days between two dates ignoring months where B1 is the start date and B2 is the end date. -- Thanks, Shane Devenshire "kaz" wrote: Could someone please help with a formula to work out the difference between two dates and show how many years, months and days there are |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formulas
i need it to show only days or months or years or all three depending on the
difference between the two dates i.e first date could be 01/02/08 and the second could be 01/04/08 which would show only 2 days, but the next set of dates could be 01/02/08 and 01/05/09 so it should show 1 year and 3 days.... are you confused because i am!! "ShaneDevenshire" wrote: Hi, =DATEDIF(B1,B2,"y") whole years between two dates =DATEDIF(B1,B2,"ym") whoe months between two dates excluding years =DATEDIF(B1,B2,"md") whole days between two dates ignoring months where B1 is the start date and B2 is the end date. -- Thanks, Shane Devenshire "kaz" wrote: Could someone please help with a formula to work out the difference between two dates and show how many years, months and days there are |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formulas
looks too long ..... try this
start date is in A1 End date is in A2 =IF((A2-A1)360,(INT((A2-A1)/360))&"year","")&" "&IF(((A2-A1)-INT((A2- A1)/360)*360)30,INT(((A2-A1)-INT((A2-A1)/360)*360)/30)&" Month","")&" "&DATEDIF(A1,A2,"d")-INT((A2-A1)/360)*360-INT(((A2-A1)-INT((A2-A1)/ 360)*360)/30)*30&" days" On Oct 16, 9:01*am, kaz wrote: i need it to show only days or months or years or all three depending on the difference between the two dates i.e *first date could be 01/02/08 and the second could be 01/04/08 which would show only 2 days, but the next set of dates could be 01/02/08 and 01/05/09 so it should show 1 year and 3 days..... * are you confused because i am!! "ShaneDevenshire" wrote: Hi, =DATEDIF(B1,B2,"y") * *whole years between two dates =DATEDIF(B1,B2,"ym") *whoe months between two dates excluding years =DATEDIF(B1,B2,"md") *whole days between two dates ignoring months where B1 is the start date and B2 is the end date. -- Thanks, Shane Devenshire "kaz" wrote: Could someone please help with a formula to work out the difference between two dates and show how many years, months and days there are- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formulas
the formula is great but it is adding on days - could it have something to do
with 30 days in some months and 31 in others etc? "muddan madhu" wrote: looks too long ..... try this start date is in A1 End date is in A2 =IF((A2-A1)360,(INT((A2-A1)/360))&"year","")&" "&IF(((A2-A1)-INT((A2- A1)/360)*360)30,INT(((A2-A1)-INT((A2-A1)/360)*360)/30)&" Month","")&" "&DATEDIF(A1,A2,"d")-INT((A2-A1)/360)*360-INT(((A2-A1)-INT((A2-A1)/ 360)*360)/30)*30&" days" On Oct 16, 9:01 am, kaz wrote: i need it to show only days or months or years or all three depending on the difference between the two dates i.e first date could be 01/02/08 and the second could be 01/04/08 which would show only 2 days, but the next set of dates could be 01/02/08 and 01/05/09 so it should show 1 year and 3 days..... are you confused because i am!! "ShaneDevenshire" wrote: Hi, =DATEDIF(B1,B2,"y") whole years between two dates =DATEDIF(B1,B2,"ym") whoe months between two dates excluding years =DATEDIF(B1,B2,"md") whole days between two dates ignoring months where B1 is the start date and B2 is the end date. -- Thanks, Shane Devenshire "kaz" wrote: Could someone please help with a formula to work out the difference between two dates and show how many years, months and days there are- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formulas
On Wed, 15 Oct 2008 21:01:00 -0700, kaz wrote:
i need it to show only days or months or years or all three depending on the difference between the two dates i.e first date could be 01/02/08 and the second could be 01/04/08 which would show only 2 days, but the next set of dates could be 01/02/08 and 01/05/09 so it should show 1 year and 3 days.... are you confused because i am!! Perhaps something like: =IF(DATEDIF(A1,A2,"y")0,DATEDIF(A1,A2,"y") & " years, ","") & IF(DATEDIF(A1,A2,"ym") 0, DATEDIF(A1,A2,"ym") & " months, ","") & IF(DATEDIF(A1,A2,"md")0, DATEDIF(A1,A2,"md")& " days") --ron |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formulas
slight change
=IF((A2-A1)=365,(INT((A2-A1)/365))&"year","")&" "&IF(((A2-A1)-INT((A2- A1)/365)*365)30,INT(((A2-A1)-INT((A2-A1)/365)*365)/30)&" Month","")&""&DATEDIF(A1,A2,"d")-INT((A2-A1)/365)*365-INT(((A2-A1)- INT((A2-A1)/365)*365)/30)*30&" days" On Oct 17, 2:38*am, kaz wrote: the formula is great but it is adding on days - could it have something to do with 30 days in some months and 31 in others etc? "muddan madhu" wrote: looks too long ..... try this start date is in A1 End date is in A2 =IF((A2-A1)360,(INT((A2-A1)/360))&"year","")&" "&IF(((A2-A1)-INT((A2- A1)/360)*360)30,INT(((A2-A1)-INT((A2-A1)/360)*360)/30)&" Month","")&" "&DATEDIF(A1,A2,"d")-INT((A2-A1)/360)*360-INT(((A2-A1)-INT((A2-A1)/ 360)*360)/30)*30&" days" On Oct 16, 9:01 am, kaz wrote: i need it to show only days or months or years or all three depending on the difference between the two dates i.e *first date could be 01/02/08 and the second could be 01/04/08 which would show only 2 days, but the next set of dates could be 01/02/08 and 01/05/09 so it should show 1 year and 3 days..... * are you confused because i am!! "ShaneDevenshire" wrote: Hi, =DATEDIF(B1,B2,"y") * *whole years between two dates =DATEDIF(B1,B2,"ym") *whoe months between two dates excluding years =DATEDIF(B1,B2,"md") *whole days between two dates ignoring months where B1 is the start date and B2 is the end date. -- Thanks, Shane Devenshire "kaz" wrote: Could someone please help with a formula to work out the difference between two dates and show how many years, months and days there are- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date Formulas | Excel Worksheet Functions | |||
| Date Formulas | Excel Worksheet Functions | |||
| how do i set up a formulas with an if date? | Excel Worksheet Functions | |||
| Date Formulas | Excel Discussion (Misc queries) | |||
| Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions | |||
Linear Mode
