software

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)lt;MONTH(G4),AND(MONTH(H4)=MONTH(G4),
DAY(H4)lt;DAY(G4))),1,0)amp;quot; years, quot;amp;MONTH(H4)-MONTH(G4) IF(AND(MONTH(H4)
lt;=MONTH(G4),DAY(H4)lt;DAY(G4)),11,IF(AND(MONTH(H4)lt;M ONTH(G4),DAY(H4)
gt;=DAY(G4)),12,IF(AND(MONTH(H4)gt;MONTH(G4),DAY(H4)lt;D AY(G4)),-1)))amp;quot; months,
quot;amp;H4-DATE(YEAR(H4),MONTH(H4)-IF(DAY(H4)lt;DAY(G4),1,0),DAY(G4))amp;quot; daysquot;

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

--
GS

i might be missing something, but

H4-G4

gives you actual time elapsed - format however you feel like (hours - days amp;
hours - days amp; hours and mins)quot;Gary F Sheltonquot; gt; wrote in message
...
gt; I have a goal to try and figure out how to Calculate Elapsed Date and Time
gt; and then format it for a specific way. Here is what I have thus far:
gt; Date Requested: [Cell G4 reads:] 03/25/1994 01:30:12 PM
gt; Date Submitted: [Cell H4 reads:] 5/1/1998 12:00:00 PM
gt;
gt; Now I can calcualte the Date Interval Elapsed with the following formula:
gt; =YEAR(H4)-YEAR(G4)-IF(OR(MONTH(H4)lt;MONTH(G4),AND(MONTH(H4)=MONTH(G4),
gt; DAY(H4)lt;DAY(G4))),1,0)amp;quot; years, quot;amp;MONTH(H4)-MONTH(G4) IF(AND(MONTH(H4)
gt; lt;=MONTH(G4),DAY(H4)lt;DAY(G4)),11,IF(AND(MONTH(H4)lt;M ONTH(G4),DAY(H4)
gt; gt;=DAY(G4)),12,IF(AND(MONTH(H4)gt;MONTH(G4),DAY(H4)lt;D AY(G4)),-1)))amp;quot; months,
gt; quot;amp;H4-DATE(YEAR(H4),MONTH(H4)-IF(DAY(H4)lt;DAY(G4),1,0),DAY(G4))amp;quot; daysquot;
gt;
gt; The result is: 4 years, 1 months, 6.5 days
gt; As you can see this only calculates the Year, Month and Days.
gt;
gt; I want the formula to include the Time information elapsed but I can't
gt; figure it out.
gt;
gt; Any assistance will be greatly appreciated.
gt; Regards,
gt; Gary Shelton
gt;
gt; --
gt; GS
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
--
GSquot;JethroUK©quot; wrote:

gt; i might be missing something, but
gt;
gt; H4-G4
gt;
gt; gives you actual time elapsed - format however you feel like (hours - days amp;
gt; hours - days amp; hours and mins)
gt;
gt;
gt; quot;Gary F Sheltonquot; gt; wrote in message
gt; ...
gt; gt; I have a goal to try and figure out how to Calculate Elapsed Date and Time
gt; gt; and then format it for a specific way. Here is what I have thus far:
gt; gt; Date Requested: [Cell G4 reads:] 03/25/1994 01:30:12 PM
gt; gt; Date Submitted: [Cell H4 reads:] 5/1/1998 12:00:00 PM
gt; gt;
gt; gt; Now I can calcualte the Date Interval Elapsed with the following formula:
gt; gt; =YEAR(H4)-YEAR(G4)-IF(OR(MONTH(H4)lt;MONTH(G4),AND(MONTH(H4)=MONTH(G4),
gt; gt; DAY(H4)lt;DAY(G4))),1,0)amp;quot; years, quot;amp;MONTH(H4)-MONTH(G4) IF(AND(MONTH(H4)
gt; gt; lt;=MONTH(G4),DAY(H4)lt;DAY(G4)),11,IF(AND(MONTH(H4)lt;M ONTH(G4),DAY(H4)
gt; gt; gt;=DAY(G4)),12,IF(AND(MONTH(H4)gt;MONTH(G4),DAY(H4)lt;D AY(G4)),-1)))amp;quot; months,
gt; gt; quot;amp;H4-DATE(YEAR(H4),MONTH(H4)-IF(DAY(H4)lt;DAY(G4),1,0),DAY(G4))amp;quot; daysquot;
gt; gt;
gt; gt; The result is: 4 years, 1 months, 6.5 days
gt; gt; As you can see this only calculates the Year, Month and Days.
gt; gt;
gt; gt; I want the formula to include the Time information elapsed but I can't
gt; gt; figure it out.
gt; gt;
gt; gt; Any assistance will be greatly appreciated.
gt; gt; Regards,
gt; gt; Gary Shelton
gt; gt;
gt; gt; --
gt; gt; GS
gt;
gt;
gt;

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,
Fredquot;Gary F Sheltonquot; gt; wrote in message
...
gt; Hi JethroUK, Yeah your solution doesn't give me what I am looking for. I am
gt; trying to come up with a submitted date and time and calculate the date and
gt; time interval of the date submitted information... But I want the answer to
gt; be formatted so answer looks something like this: 4 years, 1 month, 6.5 days,
gt; 3 hours, 12 minutes.... with the formula I have I can get as far as 4 years,
gt; 1 month, 6.5 days, but I can't figure out how to calculate the remaining time
gt; information....
gt;
gt; Regards,
gt; Gary Shelton
gt; --
gt; GS
gt;
gt;
gt; quot;JethroUK©quot; wrote:
gt;
gt;gt; i might be missing something, but
gt;gt;
gt;gt; H4-G4
gt;gt;
gt;gt; gives you actual time elapsed - format however you feel like (hours - days amp;
gt;gt; hours - days amp; hours and mins)
gt;gt;
gt;gt;
gt;gt; quot;Gary F Sheltonquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; I have a goal to try and figure out how to Calculate Elapsed Date and Time
gt;gt; gt; and then format it for a specific way. Here is what I have thus far:
gt;gt; gt; Date Requested: [Cell G4 reads:] 03/25/1994 01:30:12 PM
gt;gt; gt; Date Submitted: [Cell H4 reads:] 5/1/1998 12:00:00 PM
gt;gt; gt;
gt;gt; gt; Now I can calcualte the Date Interval Elapsed with the following formula:
gt;gt; gt; =YEAR(H4)-YEAR(G4)-IF(OR(MONTH(H4)lt;MONTH(G4),AND(MONTH(H4)=MONTH(G4),
gt;gt; gt; DAY(H4)lt;DAY(G4))),1,0)amp;quot; years, quot;amp;MONTH(H4)-MONTH(G4) IF(AND(MONTH(H4)
gt;gt; gt; lt;=MONTH(G4),DAY(H4)lt;DAY(G4)),11,IF(AND(MONTH(H4)lt;M ONTH(G4),DAY(H4)
gt;gt; gt; gt;=DAY(G4)),12,IF(AND(MONTH(H4)gt;MONTH(G4),DAY(H4)lt;D AY(G4)),-1)))amp;quot; months,
gt;gt; gt; quot;amp;H4-DATE(YEAR(H4),MONTH(H4)-IF(DAY(H4)lt;DAY(G4),1,0),DAY(G4))amp;quot; daysquot;
gt;gt; gt;
gt;gt; gt; The result is: 4 years, 1 months, 6.5 days
gt;gt; gt; As you can see this only calculates the Year, Month and Days.
gt;gt; gt;
gt;gt; gt; I want the formula to include the Time information elapsed but I can't
gt;gt; gt; figure it out.
gt;gt; gt;
gt;gt; gt; Any assistance will be greatly appreciated.
gt;gt; gt; Regards,
gt;gt; gt; Gary Shelton
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; GS
gt;gt;
gt;gt;
gt;gt;