Share via

Calculate timesheet hours in 24 hour format

Anonymous
2013-05-04T21:48:57+00:00

We use military (24 hour) time format in all of our logs, etc.  I would like to use the same 24 hr. format for our time

sheets.  Is there a method to allow hours entered without the : between hours and minutes.  For example:  A simple substation formula, out time minus in time divided by 100 gives the correct value for each day but it is in decimal format.  How can this be converted to enable a correct sum for the pay period?

DAY DATE IN OUT LUNCH HRS WORKED SICK VACATION HOLIDAY OTHER
THU 04/24/13 515 1330 **** 8.15 **** **** **** ****
FRI 04/25/13 **** **** **** 0.00 **** **** **** ****
SAT 04/26/13 **** **** **** 0.00 **** **** **** ****
SUN 04/27/13 **** **** **** 0.00 **** **** **** ****
MON 04/28/13 **** **** **** 0.00 **** **** **** ****
TUE 04/29/13 **** **** **** 0.00 **** **** **** ****
WED 04/30/13 **** **** **** 0.00 **** **** **** ****
THU 05/01/13 **** **** **** 0.00 **** **** **** ****
FRI 05/02/13 **** **** **** 0.00 **** **** **** ****
SAT 05/03/13 **** **** **** 0.00 **** **** **** ****
SUN 05/04/13 **** **** **** 0.00 **** **** **** ****
MON 05/05/13 **** **** **** 0.00 **** **** **** ****
TUE 05/06/13 **** **** **** 0.00 **** **** **** ****
WED 05/07/13 **** **** **** 0.00 **** **** **** ****
PERIOD TOTALS 8.15 **** **** **** ****
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2013-05-06T07:28:21+00:00

    Hi,

    try this sample..

    in cells A1 and B1 Start and End time (Custom Format hh:mm)

    in cell C1 lunch time (Custom Format hh:mm)

    in D1 Total  (also Custom Format hh:mm)

    e.g

    in cell A1, type

    08:10

    in cell B1

    17:30

    (lunch time 50 minutes)

    in cell C1 type

    0:50

    and..

    in cell D1 write this formula

    =B1-A1-C1

    returns....08:30 (8h and 30min)

    XXXXXXXXXXXXXXXXXX

    [Edit 19May2013]

    or

    in cell D1 (format General)

    wrire:

    =HOUR(B1-A1-C1) &"hours and " & MINUTE(B1-A1-C1)&"minutes"

    3 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-05-05T07:15:07+00:00

    The formula which delivers 8.15 is probably something like

    =(D2-C2)/100

    Change it to

    =TIME(INT(D2/100),MOD(D2,100),0)-TIME(INT(C2/100),MOD(C2,100),0)

    Or,  if you are happy to include a VBA function in the workbook or an add-in, you could use this User-Defined function:

    Function TIM(MilTime As Integer) As Date

      TIM = TimeSerial(Int(MilTime/100),MilTime Mod 100, 0)

    End Function

    and then your formula would be

    =TIM(D2)-TIM(C2)

    If the shift might go over midnight - so In=1900 Out=200 then the formulas would need adjustment:

    =IF(D2<C2,1,0)+TIME(INT(D2/100),MOD(D2,100),0)-TIME(INT(C2/100),MOD(C2,100),0)

    or

    =IF(D2<C2,1,0)+TIM(D2)-TIM(C2)

    1 person found this answer helpful.
    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2013-05-06T02:19:00+00:00

    Hi,

    Try this

    =TEXT(D2,"00:00")-TEXT(C2,"00:00")

    D2 has end time and C2 has start time.  Format resultant cell as Time.

    Hope this helps.

    0 comments
  3. OssieMac 48,001 Reputation points Volunteer Moderator
    2013-05-05T07:12:02+00:00

    If you simply want to convert the 8.15 to a decimal number of 8.25 then the following formula (where the IN time (0515) is in  B2 and the OUT time (1330) in C2.)

    =INT((C2-B2)/100)+((C2-B2)/100-INT((C2-B2)/100))*100/60

    If you want to convert the time to real time format then the following formula (where the IN time (0515) is in  B2 and the OUT time (1330) in C2.)

    =(INT((C2-B2)/100)+((C2-B2)/100-INT((C2-B2)/100))*100/60)/24

    And apply number format to cell with the result as   [hh]:mm    (Square brackets around the hh prevents times after 1pm displaying as 1:00, 2:00 etc and keeps them displaying as 13:00 etc.)

    If you want to keep the 8.15 displayed as is and then use a formula to create 8.25 in another cell  then the following (Where F2 is the cell displaying 8.15)

    =(INT(F2)+(F2-INT(F2))*100/60)/24

    0 comments