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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 01:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com