Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gary F Shelton
 
Posts: n/a
Default Calculate Elapsed Date and Time

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)<M ONTH(G4),DAY(H4)
=DAY(G4)),12,IF(AND(MONTH(H4)MONTH(G4),DAY(H4)<D AY(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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JethroUK©
 
Posts: n/a
Default Calculate Elapsed Date and Time

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" wrote in message
...
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)<M ONTH(G4),DAY(H4)
=DAY(G4)),12,IF(AND(MONTH(H4)MONTH(G4),DAY(H4)<D AY(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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gary F Shelton
 
Posts: n/a
Default Calculate Elapsed Date and Time

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" wrote in message
...
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)<M ONTH(G4),DAY(H4)
=DAY(G4)),12,IF(AND(MONTH(H4)MONTH(G4),DAY(H4)<D AY(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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Fred Smith
 
Posts: n/a
Default Calculate Elapsed Date and Time

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" wrote in message
...
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" wrote in message
...
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)<M ONTH(G4),DAY(H4)
=DAY(G4)),12,IF(AND(MONTH(H4)MONTH(G4),DAY(H4)<D AY(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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can this even be done? Tracking Date / Time Elapsed scd New Users to Excel 6 February 3rd 06 08:44 PM
How to calculate Date & Time differences robs Excel Worksheet Functions 2 October 4th 05 04:22 PM
Using VLOOKUP with a Date and Time Charles Excel Discussion (Misc queries) 4 September 20th 05 06:38 PM
How do I calculate if a date is in a certain time frame? Pe66les Excel Worksheet Functions 19 August 27th 05 11:07 PM
Do when a time and date has elapsed Michael Excel Discussion (Misc queries) 3 April 24th 05 10:46 PM


All times are GMT +1. The time now is 04:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"