Calculate Elapsed Date and Time

[Gary F Shelton]

I have a goal to try and figure out how to Calculate Elapsed Date and Time
and then format it for a specific way. Here is what I have thus far:
Date Requested: [Cell G4 reads:] 03/25/1994 01:30:12 PM
Date Submitted: [Cell H4 reads:] 5/1/1998 12:00:00 PM

Now I can calcualte the Date Interval Elapsed with the following formula:
=YEAR(H4)-YEAR(G4)-IF(OR(MONTH(H4)<MONTH(G4),AND(MONTH(H4)=MONTH(G4),
DAY(H4)<DAY(G4))),1,0)&" years, "&MONTH(H4)-MONTH(G4)+IF(AND(MONTH(H4)
<=MONTH(G4),DAY(H4)<DAY(G4)),11,IF(AND(MONTH(H4)<MONTH(G4),DAY(H4)

=DAY(G4)),12,IF(AND(MONTH(H4)>MONTH(G4),DAY(H4)<DAY(G4)),-1)))&" months,

"&H4-DATE(YEAR(H4),MONTH(H4)-IF(DAY(H4)<DAY(G4),1,0),DAY(G4))&" days"

The result is: 4 years, 1 months, 6.5 days
As you can see this only calculates the Year, Month and Days.

I want the formula to include the Time information elapsed but I can't
figure it out.

Any assistance will be greatly appreciated.
Regards,
Gary Shelton

 

[JethroUK©]

i might be missing something, but

H4-G4

gives you actual time elapsed - format however you feel like (hours - days &
hours - days & hours and mins)

I have a goal to try and figure out how to Calculate Elapsed Date and Time
and then format it for a specific way. Here is what I have thus far:
Date Requested: [Cell G4 reads:] 03/25/1994 01:30:12 PM
Date Submitted: [Cell H4 reads:] 5/1/1998 12:00:00 PM

Now I can calcualte the Date Interval Elapsed with the following formula:
=YEAR(H4)-YEAR(G4)-IF(OR(MONTH(H4)<MONTH(G4),AND(MONTH(H4)=MONTH(G4),
DAY(H4)<DAY(G4))),1,0)&" years, "&MONTH(H4)-MONTH(G4)+IF(AND(MONTH(H4)
<=MONTH(G4),DAY(H4)<DAY(G4)),11,IF(AND(MONTH(H4)<MONTH(G4),DAY(H4)

=DAY(G4)),12,IF(AND(MONTH(H4)>MONTH(G4),DAY(H4)<DAY(G4)),-1)))&" months,

"&H4-DATE(YEAR(H4),MONTH(H4)-IF(DAY(H4)<DAY(G4),1,0),DAY(G4))&" days"

The result is: 4 years, 1 months, 6.5 days
As you can see this only calculates the Year, Month and Days.

I want the formula to include the Time information elapsed but I can't
figure it out.

Any assistance will be greatly appreciated.
Regards,
Gary Shelton

 

[Gary F Shelton]

Hi JethroUK, Yeah your solution doesn't give me what I am looking for. I am
trying to come up with a submitted date and time and calculate the date and
time interval of the date submitted information... But I want the answer to
be formatted so answer looks something like this: 4 years, 1 month, 6.5 days,
3 hours, 12 minutes.... with the formula I have I can get as far as 4 years,
1 month, 6.5 days, but I can't figure out how to calculate the remaining time
information....

Regards,
Gary Shelton
--
GS

i might be missing something, but

H4-G4

gives you actual time elapsed - format however you feel like (hours - days &
hours - days & hours and mins)

I have a goal to try and figure out how to Calculate Elapsed Date and Time
and then format it for a specific way. Here is what I have thus far:
Date Requested: [Cell G4 reads:] 03/25/1994 01:30:12 PM
Date Submitted: [Cell H4 reads:] 5/1/1998 12:00:00 PM

Now I can calcualte the Date Interval Elapsed with the following formula:
=YEAR(H4)-YEAR(G4)-IF(OR(MONTH(H4)<MONTH(G4),AND(MONTH(H4)=MONTH(G4),
DAY(H4)<DAY(G4))),1,0)&" years, "&MONTH(H4)-MONTH(G4)+IF(AND(MONTH(H4)
<=MONTH(G4),DAY(H4)<DAY(G4)),11,IF(AND(MONTH(H4)<MONTH(G4),DAY(H4)

=DAY(G4)),12,IF(AND(MONTH(H4)>MONTH(G4),DAY(H4)<DAY(G4)),-1)))&" months,

"&H4-DATE(YEAR(H4),MONTH(H4)-IF(DAY(H4)<DAY(G4),1,0),DAY(G4))&" days"

The result is: 4 years, 1 months, 6.5 days
As you can see this only calculates the Year, Month and Days.

I want the formula to include the Time information elapsed but I can't
figure it out.

Any assistance will be greatly appreciated.
Regards,
Gary Shelton

 

[Fred Smith]

Why doesn't Jethro's solution work? Did you try it? To get the display you want,
use a custom format of: yy/mm/dd hh:mm:ss

--
Regards,
Fred

Hi JethroUK, Yeah your solution doesn't give me what I am looking for. I am
trying to come up with a submitted date and time and calculate the date and
time interval of the date submitted information... But I want the answer to
be formatted so answer looks something like this: 4 years, 1 month, 6.5 days,
3 hours, 12 minutes.... with the formula I have I can get as far as 4 years,
1 month, 6.5 days, but I can't figure out how to calculate the remaining time
information....

Regards,
Gary Shelton
--
GS

i might be missing something, but

H4-G4

gives you actual time elapsed - format however you feel like (hours - days &
hours - days & hours and mins)

I have a goal to try and figure out how to Calculate Elapsed Date and Time
and then format it for a specific way. Here is what I have thus far:
Date Requested: [Cell G4 reads:] 03/25/1994 01:30:12 PM
Date Submitted: [Cell H4 reads:] 5/1/1998 12:00:00 PM

Now I can calcualte the Date Interval Elapsed with the following formula:
=YEAR(H4)-YEAR(G4)-IF(OR(MONTH(H4)<MONTH(G4),AND(MONTH(H4)=MONTH(G4),
DAY(H4)<DAY(G4))),1,0)&" years, "&MONTH(H4)-MONTH(G4)+IF(AND(MONTH(H4)
<=MONTH(G4),DAY(H4)<DAY(G4)),11,IF(AND(MONTH(H4)<MONTH(G4),DAY(H4)
=DAY(G4)),12,IF(AND(MONTH(H4)>MONTH(G4),DAY(H4)<DAY(G4)),-1)))&" months,
"&H4-DATE(YEAR(H4),MONTH(H4)-IF(DAY(H4)<DAY(G4),1,0),DAY(G4))&" days"

The result is: 4 years, 1 months, 6.5 days
As you can see this only calculates the Year, Month and Days.

I want the formula to include the Time information elapsed but I can't
figure it out.

Any assistance will be greatly appreciated.
Regards,
Gary Shelton