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

On 2002-09-10 08:31, gymwrecker wrote:
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!!!

Whoa!! How does 2001 + 2006 give you 2008? Are you redefining the nature of your values in cells A1 and A2? Let's not go down this path again without a full explanation!

 

[gymwrecker]

hahahaha! No, it's different! What I'm doing is adding periods of time, for example, the first period is not 2001, it's 1 year, 8 months and 20 days, then the other one is 6 years, 7 months and 23 days, if you add them up, you come up with 8 years, not 2008, and so on.... I need a formula that converts over 30 days to months, over 12 months to years....

Thanks Mark

 

[Mark W.]

{=TEXT(INT((SUM(MID(A1,{1,4,7},2)*{360,30,1})+SUM(MID(A2,{1,4,7},2)*{360,30,1}))/360),"00 ")&TEXT(INT(MOD((SUM(MID(A1,{1,4,7},2)*{360,30,1})+SUM(MID(A2,{1,4,7},2)*{360,30,1}))/360,1)*12),"00 ")&TEXT(MOD((SUM(MID(A1,{1,4,7},2)*{360,30,1})+SUM(MID(A2,{1,4,7},2)*{360,30,1}))/30,1)*30,"00")}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

 

[gymwrecker]

Thanks Mark! I still have problems with the first formula, but will get back with you later!

 

[PaddyD]

given the above, I shall not be pursuing:

http://mrexcel.com/board/viewtopic.php?topic=21628&forum=2&7

 

[Mark W.]

On 2002-09-10 10:04, Mark W. wrote:
{=TEXT(INT((SUM(MID(A1,{1,4,7},2)*{360,30,1})+SUM(MID(A2,{1,4,7},2)*{360,30,1}))/360),"00 ")&TEXT(INT(MOD((SUM(MID(A1,{1,4,7},2)*{360,30,1})+SUM(MID(A2,{1,4,7},2)*{360,30,1}))/360,1)*12),"00 ")&TEXT(MOD((SUM(MID(A1,{1,4,7},2)*{360,30,1})+SUM(MID(A2,{1,4,7},2)*{360,30,1}))/30,1)*30,"00")}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

BTW the above formula can be simplified to...

{=TEXT(INT((SUM(MID(A1:A2,{1,4,7},2)*{360,30,1}))/360),"00 ")&TEXT(INT(MOD((SUM(MID(A1:A2,{1,4,7},2)*{360,30,1}))/360,1)*12),"00 ")&TEXT(MOD((SUM(MID(A1:A2,{1,4,7},2)*{360,30,1}))/30,1)*30,"00")}


And, the difference formula published earlier can be simplified to...

{=TEXT(INT((SUM(MID(A1:A2,{1,4,7},2)*{360,30,1}*{1;-1}))/360),"00 ")&TEXT(INT(MOD((SUM(MID(A1:A2,{1,4,7},2)*{360,30,1}*{1;-1}))/360,1)*12),"00 ")&TEXT(MOD((SUM(MID(A1:A2,{1,4,7},2)*{360,30,1}*{1;-1}))/30,1)*30,"00")}
This message was edited by Mark W. on 2002-09-10 15:14

 

[gymwrecker]

Thank You!