Time formulas
Hours between times, night shifts, overtime, breaks and pay, rounding to 15 minutes, and converting decimal hours. 31 answers, each checked by running it, with a live example and an exercise.
How to calculate the hours between two times in Excel
=(C2-B2)*24Subtract the start from the end and multiply by 24:
=(C2-B2)*24. Times are fractions of a day, so the 24 turns the difference into hours.How to calculate time worked across midnight in Excel
=MOD(C2-B2,1)*24Wrap the difference in MOD:
=MOD(C2-B2,1)*24. A negative overnight difference becomes the right number of hours.How to calculate the time difference in minutes in Excel
=(C2-B2)*1440Multiply the difference by the 1,440 minutes in a day:
=(C2-B2)*1440.How to calculate hours between two dates and times in Excel
=(C2-B2)*24Subtract the two date-times and multiply by 24:
=(C2-B2)*24. It works across midnight and across several days.How to convert decimal hours to hours and minutes in Excel
=B2/24Divide by 24 and format the cell as h:mm:
=B2/24turns 7.75 into 7:45.How to convert time to decimal hours in Excel
=B2*24Multiply the time by 24:
=B2*24turns 7:45 into 7.75 hours.How to convert minutes to hours and minutes in Excel
=B2/1440Divide by 1,440, the minutes in a day, and format as h:mm:
=B2/1440turns 135 into 2:15.How to convert time to minutes in Excel
=B2*1440Multiply by 1,440, the minutes in a day:
=B2*1440turns 1:15 into 75.How to convert a time to seconds in Excel
=B2*86400Multiply by 86,400, the seconds in a day:
=B2*86400.How to convert seconds to minutes and seconds (mm:ss) in Excel
=B2/86400Divide by 86,400 and format as mm:ss:
=B2/86400shows 95 seconds as 01:35.How to calculate overtime hours over 8 hours a day in Excel
=MAX(0,(C2-B2)*24-$G$1)Take the hours worked minus 8, but never below zero:
=MAX(0,(C2-B2)*24-$G$1), with 8 in G1.How to calculate overtime pay over 40 hours in Excel
=MIN(B2,$G$1)*C2+MAX(0,B2-$G$1)*C2*$G$2Pay the first 40 hours at the rate and the rest at 1.5 times:
=MIN(B2,40)*C2+MAX(0,B2-40)*C2*1.5, with 40 and 1.5 in cells.How to round time to the nearest 15 minutes in Excel
=MROUND(B2,"0:15")Use MROUND with a 15-minute step:
=MROUND(B2,"0:15").How to round billable time up to the nearest tenth of an hour in Excel
=ROUNDUP(B2/60,1)Convert minutes to hours and round up to one decimal:
=ROUNDUP(B2/60,1). Every started 6 minutes bills as 0.1 hour.How to add minutes to a time in Excel
=B2+C2/1440Divide the minutes by 1,440 and add them:
=B2+C2/1440.How to add hours to a date and time in Excel
=B2+C2/24Divide the hours by 24 and add them:
=B2+C2/24. It rolls into the next day when needed.How to extract the time from a date and time in Excel
=MOD(A2,1)Keep the fraction of the day:
=MOD(A2,1), formatted as a time.How to combine a date and a time into one cell in Excel
=B2+C2Add them:
=B2+C2. A date is a whole number and a time is a fraction, so the sum holds both.How to calculate hours worked minus a lunch break in Excel
=(C2-B2)*24-D2/60Subtract the break from the time on site:
=(C2-B2)*24-D2/60, with the break in minutes in D2.How to multiply hours and minutes by an hourly rate in Excel
=B2*24*C2Convert the time to hours first:
=B2*24*C2. Without *24 the pay comes out 24 times too small.How to calculate the average time in Excel
=AVERAGE(B2:B8)Use AVERAGE and format the result as a time:
=AVERAGE(B2:B8).How to convert text to time in Excel
=TIMEVALUE(A2)Use
=TIMEVALUE(A2). It turns text like "2:45 PM" into a real time you can format and calculate with.How to convert military time to standard time in Excel
=INT(A2/100)/24+MOD(A2,100)/1440Split the number into hours and minutes:
=INT(A2/100)/24+MOD(A2,100)/1440, then format as h:mm AM/PM.How to check if a time is between two times in Excel
=IF(MOD(B2-$F$1,1)<MOD($F$2-$F$1,1),"Yes","No")Measure from the start time with MOD:
=IF(MOD(B2-$F$1,1)<MOD($F$2-$F$1,1),"Yes","No"). It works for windows that cross midnight.How to calculate how many minutes late someone is in Excel
=MAX(0,(B2-$F$1)*1440)Subtract the start time, convert to minutes and floor at zero:
=MAX(0,(B2-$F$1)*1440).How to total hours per employee from a timesheet in Excel
=SUMPRODUCT(($A$2:$A$10=F2)*($D$2:$D$10-$C$2:$C$10))*24Sum each person's out-minus-in with SUMPRODUCT and convert to hours:
=SUMPRODUCT(($A$2:$A$10=F2)*($D$2:$D$10-$C$2:$C$10))*24.How to calculate running pace per mile in Excel
=C2/B2Divide the time by the distance and format as mm:ss:
=C2/B2.How to convert time zones in Excel
=$F$1+B2/24Add the UTC offset as a fraction of a day:
=$F$1+B2/24, with the UTC time in F1 and the offset in hours in B2.How to show a duration as days, hours and minutes in Excel
=INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m""")Take the whole days with INT and format the rest with TEXT:
=INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m""").How to calculate working hours between two dates and times in Excel
=((NETWORKDAYS(B2,C2)-1)*($G$2-$G$1)+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),$G$1,$G$2),$G$2)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),$G$1,$G$2))*24Count full working days with NETWORKDAYS and trim the first and last day to opening hours with MEDIAN.
How to find the first clock-in time for each day in Excel
=MINIFS($A$2:$A$10,$A$2:$A$10,">="&C2,$A$2:$A$10,"<"&C2+1)Use MINIFS with a one-day window:
=MINIFS(punches,punches,">="&C2,punches,"<"&C2+1).