I'm trying to run a time-sheet import excel sheet to our software. It has columns as follows: 'employee name', 'start time', 'end time', and 'total hours'. Problem is that some 'start time' and 'end time' rows don't have AM/PM. When i edit the formatting of the cell it should show if it's AM or PM it just adds AM to all.
example: start time 8:00 end time 3:30 total hours 7.5
In the above example, the end time 3:30 is clearly PM since total hours is 7.5.
Question: is there a formula in excel to add in the start/end time cells whether this time is AM or PM ACCORDING TO THE TOTAL HOURS?
13 Answers
Select the following Time format.
The PM time format shows AM and PM
Notice the sample is showing AM for the data currently in the cell.
Import: Use an Excel Sheet, Helper-Column Instead of End Time column.
End Time Helper-Column =[StartTime] + [TimeWork]
- Depending on the software doing the import, this may be a solution
available in the import script or the import configuration settings.
Further refinement when start times occur in the PM:
Start Time Helper-Column =If([StartTime] > [AM/PM-CutOff], [StartTime], [StartTime] + TIME(12,0,0))
- Where [AM/PM-CutOff] is
Time(Hours,Mins,Secs) - After what hour:Min is the Start time definitely AM? (Is 4:00 an AM or PM start time?)
- The cut off could be further refined based on the hours worked.
Basically this happens due to improper TIME Format, applied to cells.
How it works:
- Select Start & End Time Cells, and apply
HH:MM AM/PMFormat. - Apply
[H]:MMTime Format to cells, before you apply Formula to get Time Difference.
N.B.
- While enter Time in cells, the Time must according to 24 Hrs Clock, for example
15:30for03:30 PM, since cells hasTIME Format HH:MM AM/PM. - In case both
Start & End Time Cells has HH:MM AM/PMFormat and theFormula cells has GENERALbetter use this Formula.
=(EndTime-StartTime)*24.
You get 7.5.
