# Time formulas in Excel and Google Sheets

> Hours between times, night shifts, overtime, breaks and pay, rounding to 15 minutes, and converting decimal hours. 31 answers, each checked by running it.

Source: https://spreadsheetpractice.com/formulas/topic/time · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Answers

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

More topics: [Counting formulas](https://spreadsheetpractice.com/formulas/topic/count) · [Sum formulas](https://spreadsheetpractice.com/formulas/topic/sum) · [Lookup formulas](https://spreadsheetpractice.com/formulas/topic/lookup) · [IF and logic formulas](https://spreadsheetpractice.com/formulas/topic/logic) · [Text formulas](https://spreadsheetpractice.com/formulas/topic/text) · [Date formulas](https://spreadsheetpractice.com/formulas/topic/date) · [Dynamic array formulas](https://spreadsheetpractice.com/formulas/topic/dynamic) · [Duplicates and data cleanup formulas](https://spreadsheetpractice.com/formulas/topic/cleanup) · [Rounding and math formulas](https://spreadsheetpractice.com/formulas/topic/math) · [Percentage formulas](https://spreadsheetpractice.com/formulas/topic/percent) · [Formula error fixes](https://spreadsheetpractice.com/formulas/topic/errors) · [Google Sheets formulas](https://spreadsheetpractice.com/formulas/topic/sheets) · [Finance formulas](https://spreadsheetpractice.com/formulas/topic/finance)

Every answer: [Formula answers](https://spreadsheetpractice.com/formulas). Formulas used: `TEXT`, `MOD`, `ROUND`, `INT`, `MAX`, `MIN`, `MROUND`, `FLOOR`, `CEILING`, `ROUNDUP`, `IF`, `AVERAGE`, `SUM`, `COUNT`, `TIMEVALUE`, `VALUE`, `LEFT`, `RIGHT`, `OR`, `AND`, `SUMPRODUCT`, `FILTER`, `NETWORKDAYS`, `MEDIAN`, `LET`, `MINIFS`, `MAXIFS`

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.