Need to Calculate Total Hours from 2 date cells and 2 time cells in military time.
The data is downloaded from the medical records. It puts the Admitted Date in C2 (10/1/2014) and Admit Time in D2 (605) should be 0605 anyway. Then Discharge Date in E2 (10/4/2015) and Discharge Time in F2 (1320). I am having problems getting it to calculate the total hours over the date span in military time. I really need help.
.
Hello, can you post a sample workbook writing the expected input and output?
"The quieter you become, the more you are able to hear"
Any reputation (*) points appreciated.
"If you know yourself but not the enemy, for every victory gained, you will suffer defeat."
Re: Need to Calculate Total Hours from 2 date cells and 2 time cells in military time.
Test.xlsx Here is a sample workbook of data. Thanks
See if this starts things in the right direction. Put in H2Then format Custom [h]:mm.Formula:
Please Login or Register to view this content.
Are we close?
Use same formula/format G2(different dates, thoughFormula:Please Login or Register to view this content.)
How about this?
-Strother
Solution 1.xlsx
Yes you got the time right, can you have it round to the nearest hour so if it comes back as 28:50 hours then it rounds to 28 hours but if it is 28:51 hours it takes it to 29 hours?
Thank you so much for the help both solutions work! I have been killing myself over this for 4 days.... Thanks again now my presentation for tuesday is done!
I really appreciate all the help!
I am sorry to bug you again about this but this am when I plugged in the formula I am getting an error with only a few cells. it is when the admit time starts with a zero. Example (0:37). Is there a simple fix to this?
I am using this formula: =($E2+REPLACE($F2,LEN($F2)-1,0,":"))-($C2+REPLACE($D2,LEN($D2)-1,0,":"))
Otherwise it works great!
I didn't see that one coming. Apologies.Originally Posted by psunursingguy21
The original post indicates the "times" present in the format 605 and 1320. Assuming 0:37 arrives as 37 this modified version of that formula works.
Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 06-07-2015 at 03:06 PM.
Try this formula to give the exact time difference
=TEXT(F2,"00\:00")+E2-TEXT(D2,"00\:00")-C2
....or if you want it rounded to the nearest hour
=ROUND((TEXT(F2,"00\:00")+E2-TEXT(D2,"00\:00")-C2)*24,0)/24
Audere est facere
@ daddylonglegs
Yeah...much better!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks