# Date In Year, Month and Day

#### kumara_faith

##### Well-known Member
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.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### VoG

##### Legend
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

##### MrExcel MVP, Moderator
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

##### Active Member
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 ",".

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

Hope is of help to you.

Paul

#### kumara_faith

##### Well-known Member
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

##### Well-known Member
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.

bump.

#### barry houdini

##### MrExcel MVP
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"

Replies
7
Views
432
Replies
5
Views
611
Replies
12
Views
371
Replies
18
Views
456
Replies
1
Views
243

1,191,399
Messages
5,986,365
Members
440,020
Latest member
IfsandSums

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back