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

--
GS

[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)


"Gary F Shelton" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> GS

[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


"JethroUK©" wrote:

> 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)
>
>
> "Gary F Shelton" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > --
> > GS
>
>
>

[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


"Gary F Shelton" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "JethroUK©" wrote:
>
>> 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)
>>
>>
>> "Gary F Shelton" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> > --
>> > GS
>>
>>
>>