Dates

[gymwrecker]

I have 02 09 06 in cell A1 and 01 07 23 in A2, is there a formula to calculate the diference between A1-A2? Result must be in the following format: # yrs #months # days

Thanks!

 

[Mark W.]

If you had entered your dates as datevalues (e.g., 86/8/20) the formula could be simplified as follows...

=TEXT(DATEDIF(A2,A1,"y"),"00 ")&TEXT(DATEDIF(A2,A1,"ym"),"00 ")&TEXT(DATEDIF(A2,A1,"md"),"00 ")

 

[Dave Patton]

Slightly different format with real dates
in A2 and B2

=DATEDIF(A2,B2,"y")& "y "&DATEDIF(A2,B2,"ym")&"m " & DATEDIF(A2,B2,"md") & "d"

16y 0m 20d

 

[eliW]

On 2002-09-09 18:15, Dave Patton wrote:

Slightly different format with real dates
in A2 and B2

=DATEDIF(A2,B2,"y")& "y "&DATEDIF(A2,B2,"ym")&"m " & DATEDIF(A2,B2,"md") & "d"

16y 0m 20d

.......Which is very near to my first original reply - far above... and before the "storm"

Hi,
Try =DATEDIF(B1,A1,"d")
Eli
<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=5><FONT COLOR=WHITE>Microsoft Excel - Book1_______________Running: xl97 : OS = Windows (32-bit) 4.10</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>C1</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=2 BGCOLOR=White>=DATEDIF(B1,A1,"d")</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>02 09 06</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>01 07 23</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=DATEDIF(B1,A1,"d")")><FONT FACE=Arial COLOR=#000000>410</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=5><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>To see the formula in the cells just click on the cells hyperlink</FONT>

<FONT COLOR=#339966 SIZE=1>The above image was automatically generated by [HtmlMaker V1.23]</FONT>
<FONT COLOR=#339966 SIZE=1>If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you.</FONT>
<FONT COLOR=#339966 SIZE=1>This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo</FONT>
</CENTER>

 

[gymwrecker]

This formula works fine, but need one more adjustment. I need to add one day to the final result, for example:

if I have in A3 16 02 02, I want for the formula to automatically add 1 day, which will make it look like 16 02 03. This will be like that for all the substractions.

This is the formula:
=TEXT(DATEDIF(DATE(IF(LEFT(C3,2)<"29",20,19)&LEFT(C3,2),MID(C3,4,2),RIGHT(C3,2)),DATE(IF(LEFT(C2,2)<"29",20,19)&LEFT(C2,2),MID(C2,4,2),RIGHT(C2,2)),"y"),"00 ")&TEXT(DATEDIF(DATE(IF(LEFT(C3,2)<"29",20,19)&LEFT(C3,2),MID(C3,4,2),RIGHT(C3,2)),DATE(IF(LEFT(C2,2)<"29",20,19)&LEFT(C2,2),MID(C2,4,2),RIGHT(C2,2)),"ym"),"00 ")&TEXT(DATEDIF(DATE(IF(LEFT(C3,2)<"29",20,19)&LEFT(C3,2),MID(C3,4,2),RIGHT(C3,2)),DATE(IF(LEFT(C2,2)<"29",20,19)&LEFT(C2,2),MID(C2,4,2),RIGHT(C2,2)),"md"),"00")

 

[Corticus]

This should do it(I hope):
=TEXT(DATEDIF(DATE(IF(LEFT(A2,2)<"29",20,19)&LEFT(A2,2),MID(A2,4,2),RIGHT(A2,2)),DATE(IF(LEFT(A1,2)<"29",20,19)&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)),"y"),"00 ")&TEXT(DATEDIF(DATE(IF(LEFT(A2,2)<"29",20,19)&LEFT(A2,2),MID(A2,4,2),RIGHT(A2,2)),DATE(IF(LEFT(A1,2)<"29",20,19)&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)),"ym"),"00 ")&TEXT(DATEDIF(DATE(IF(LEFT(A2,2)<"29",20,19)&LEFT(A2,2),MID(A2,4,2),RIGHT(A2,2)),DATE(IF(LEFT(A1,2)<"29",20,19)&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))+1,"md"),"00")

Wow, what an elegant formula!
Corticus

BTW,
could I suggest you convert what dates you have into a standard date format, if you need a formula for this:
=VALUE((RIGHT(SUBSTITUTE(TRIM(A1)," ","/"),5)&"/"&LEFT(TRIM(A1),2)))
should do it
you could then copy-paste these resutlts over the current data,
once done, all date mainpulation can be handled using the 1901 date-system thingy, which is much easier than this crazy formula!

 

[Mark W.]

This is the formula:
=TEXT(DATEDIF(DATE(IF(LEFT(C3,2)<"29",20,19)&LEFT(C3,2),MID(C3,4,2),RIGHT(C3,2)),DATE(IF(LEFT(C2,2)<"29",20,19)&LEFT(C2,2),MID(C2,4,2),RIGHT(C2,2)),"y"),"00 ")&TEXT(DATEDIF(DATE(IF(LEFT(C3,2)<"29",20,19)&LEFT(C3,2),MID(C3,4,2),RIGHT(C3,2)),DATE(IF(LEFT(C2,2)<"29",20,19)&LEFT(C2,2),MID(C2,4,2),RIGHT(C2,2)),"ym"),"00 ")&TEXT(DATEDIF(DATE(IF(LEFT(C3,2)<"29",20,19)&LEFT(C3,2),MID(C3,4,2),RIGHT(C3,2)),DATE(IF(LEFT(C2,2)<"29",20,19)&LEFT(C2,2),MID(C2,4,2),RIGHT(C2,2)),"md"),"00")

gymwrecker, you should really test the 2-digit year using <"30" to insure conformance with Excel's two-digit year handling as described in the Excel help topic for "How Microsoft Excel handles dates in the year 2000 and beyond"...

How Excel interprets two-digit years By default, when you enter a two-digit year value, Excel interprets the year as follows:

00 through 29 Excel interprets the two-digit year values 00 through 29 as the years 2000 through 2029. For example, if you type the date 5/28/19, Excel assumes the date is May 28, 2019.


30 through 99 Excel interprets the two-digit year values 30 through 99 as the years 1930 through 1999. For example, if you type the date 5/28/98, Excel assumes the date is May 28, 1998.

 

[Mark W.]

BTW,
could I suggest you convert what dates you have into a standard date format, if you need a formula for this:
=VALUE((RIGHT(SUBSTITUTE(TRIM(A1)," ","/"),5)&"/"&LEFT(TRIM(A1),2)))
should do it
you could then copy-paste these resutlts over the current data,
once done, all date mainpulation can be handled using the 1901 date-system thingy, which is much easier than this crazy formula!

The date conversions can be accomplished simply by using the Data | Text to Columns... menu command specifying the data format as Date [YMD].

 

[gymwrecker]

Mark, could you modify the formula to have it add an extra day (default) when I obtain the results? For instance, when I obtain the following: 02 06 23, I need to add an extra day and have it read 02 06 24, but want this to be done automatically with the formula. Note: See formula in previous replies.

Thanks

 

[Mark W.]

=TEXT(DATEDIF(DATE(IF(LEFT(A2,2)<"30",20,19)&LEFT(A2,2),MID(A2,4,2),RIGHT(A2,2)),DATE(IF(LEFT(A1,2)<"30",20,19)&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)),"y"),"00 ")&TEXT(DATEDIF(DATE(IF(LEFT(A2,2)<"30",20,19)&LEFT(A2,2),MID(A2,4,2),RIGHT(A2,2)),DATE(IF(LEFT(A1,2)<"30",20,19)&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)),"ym"),"00 ")&TEXT(DATEDIF(DATE(IF(LEFT(A2,2)<"30",20,19)&LEFT(A2,2),MID(A2,4,2),RIGHT(A2,2)),DATE(IF(LEFT(A1,2)<"30",20,19)&LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)),"md")+1,"00")

 

[gymwrecker]

Thank you very much Mark!

Now, how could the formula be when I have to add periods of time in the following format:

01 08 20 (one year, 8 months, 20 days)
06 07 23 (6 years, 7 months, 23 days)
________
07 15 43

Obviously, this is 8yrs 4mnts 13days (this is how I would like the result, 08 04 13)

Thanks Again!!!