Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can this even be done? Tracking Date / Time Elapsed | New Users to Excel | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Do when a time and date has elapsed | Excel Discussion (Misc queries) |