Date In Year, Month and Day

[kumara_faith]

Hi,

I have two dates. Say Jan 1 2005 to March 12 2007.

I would like to build a formula to result in year, month and day.

Example is the formula should result in 2.2.12 ( 2 years, 2 month and 12 days)

Is there way ?

Appreciate help.

 

[VoG]

There is a way but I get 2.2.11

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:111px;" /><col style="width:102px;" /><col style="width:89px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">01/01/2005</td><td style="text-align:right; ">12/03/2007</td><td style="text-align:right; ">2.2.11</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=DATEDIF(A1,B1,"y")&"."&DATEDIF(A1,B1,"ym")&"."&DATEDIF(A1,B1,"md")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Peter_SSs]

One thing you would need to think about and clarify is what result would you want if the dates are, say, 30 Apr 2007 and 31 May 2007.

a) Is this 0.1.0 because it goes exactly from the end of one month to the end of the next month?

b) Or is this 0.1.1 because you want to count 30 Apr to 30 May as 1 month and then there is one more day?

(VoG II's formula gives 0.1.1 for these two dates)

 

[paulrm906]

hello kumara_faith

Not too sure if your problem as been solved or not, but if not then maybe the below formula should help. If someone started on the 01/02/07 and finished on the 14/06/2008 the result should look like 1 y;4 m;13 d. But first you need to change all of the ";" to "," in the formula if your computer uses ",".

=DATEDIF(A1;A2;"y") & " y; " & DATEDIF(A1;A2;"ym") & " m; " & DATEDIF(A1;A2;"md") & " d"

Hope is of help to you.

Paul

 

[kumara_faith]

VoG II,

Thank you for your solution . Appreciate your time and patience.This is what I was looking for.

Peter_SSs,

VoG II formula which results in 0.1.1 would be fine for my scenario. Thank you again for taking the time to explore further into my question. Appreciate your time and patience.

paulrm906,

Thank you for your solution. Yes, showing the text for year, month and day will work better.
Thank you again for your time and patience.

 

[kumara_faith]

Hi there,

How do I modify the formula to exclude weekends and holidays?I tried the following but it did not work.

=NETWORKDAYS(A1,A2)-(DATEDIF(A1,A2,"y") & " year, " & DATEDIF(A1,A2,"ym") & " month, " & DATEDIF(A1,A2,"md") & " day")

Appreciate help.

 

[kumara_faith]

bump.

 

[barry houdini]

It doesn't seem very logical to me to exclude weekends and holidays and then count in years months and days, is that what you mean?

If you count just days it would be

=NETWORKDAYS(A1,B1,holidays)

where holidays is a named range containing your holiday dates.

If A1 is 1-jan-2005 and B1 is 12-mar-2007 as per your example then you get a result of approximately 555 depending on how many holidays you have. How would you want to represent 555 days as y m d?

One possibility might be

=DATEDIF(0,NETWORKDAYS(A1,B1),"y")&"y "&DATEDIF(0,NETWORKDAYS(A1,B1),"ym")&"m "&DATEDIF(0,NETWORKDAYS(A1,B1),"md")&"d"