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.]

Copy the formula from the HTML form. Select an empty cell in your worksheet, click on the formula bar, paste, delete the outer-most braces { }, and simultaneously press the Shift+Control+Enter keys.

I trust that you understand that my formula is based on a standard month with 30 days.
This message was edited by Mark W. on 2002-09-09 16:32

 

[Juan Pablo González]

RANDBETWEEN is a formula included in the Analysis ToolPack AddIn. Make sure you have it installed.

 

[Mark W.]

Juan, RANDBETWEEN isn't a part of the solution. I used it to create some sample data that would demonstrate the reliability of my formula. My solution is in cell A3.
This message was edited by Mark W. on 2002-09-09 16:34

 

[gymwrecker]

I did paste it, but I get the following error when I try to use 2002:

02 01 01
86 08 20
-85 04 11

??????????

 

[Mark W.]

Of course, I believed that your data was formatted as...

# yrs #months # days

...which was read as "number of years, number of months, numbers of days", not as...

yy mm dd

If you subtract 2 years, 1 month, 1 day from 86 years, 8 months, 20 days you get -85 years, 4 months, 11 days!!!!

You also never responded to either clarifying question posed by me or Aladin.
This message was edited by Mark W. on 2002-09-09 17:12

 

[Juan Pablo González]

On 2002-09-09 16:33, Mark W. wrote:
Juan, RANDBETWEEN isn't a part of the solution. I used it to create some sample data that would demonstrate the reliability of my formula. My solution is in cell A3.
This message was edited by Mark W. on 2002-09-09 16:34

Oops, sorry Mark...

 

[Mark W.]

gymwrecker, perhaps all that you needed was...

=TEXT(DATEDIF(DATE(LEFT(A1,2),MID(A1,4,2),LEFT(A1,2)),DATE(LEFT(A2,2),MID(A2,4,2),LEFT(A2,2)),"y"),"00 ")&TEXT(DATEDIF(DATE(LEFT(A1,2),MID(A1,4,2),LEFT(A1,2)),DATE(LEFT(A2,2),MID(A2,4,2),LEFT(A2,2)),"ym"),"00 ")&TEXT(DATEDIF(DATE(LEFT(A1,2),MID(A1,4,2),LEFT(A1,2)),DATE(LEFT(A2,2),MID(A2,4,2),LEFT(A2,2)),"md"),"00")

However, I can't be sure because I don't know enough about the contents of A1:A2!!!

Oops! Some errors in the formula... see below.
This message was edited by Mark W. on 2002-09-09 17:55

 

[gymwrecker]

This works, but it's not exactly what I'm looking for; let me explain myself a little better: I need to know the number of years, months, and days between two days, let's say I joined the Army on 20 August 1986, and today, 9 September 2002, I would like to know exactly how much time has passed since then. I manually calculated it, and came up with the following result:
16 years, 00 months, and 19 days. I need a formula that can be able to do that for me.
Thanks Mark!!

 

[Mark W.]

After fixing some errors introduced by cutting & pasting to create my revised formula, I get 16 years 00 months 20 days using the following formula...

=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"),"00")

Which, BTW agrees with Corticus' original formula.

Please, next time don't leave us guessing as to the nature of your data.

Also, why on earth would you enter date values as "86 08 20" instead of 8/20/86 or (if your Regional settings specify yy/mm/dd format) as 86/8/20?
This message was edited by Mark W. on 2002-09-09 17:44

 

[gymwrecker]

Got it! Thanks Mark!