BuildQuantify logoBuildQuantify
Excel Tutorials / Time & Day Calculations in Excel
SCHEDULING

Time & Day Calculations in Excel

9 min read · Template referenced below

Beyond charting a Gantt-style schedule, most project timelines come down to simple date math: how many days until this is due, what date do we finish if we start Monday, and how many hours did the crew actually work. Excel has purpose-built functions for all three that most people don't know exist.

1
Calculate days between two dates.
With a start date in A2 and end date in B2, =B2-A2 gives the number of days between them. Format the result cell as Number (not Date) — Excel defaults to showing it as a date otherwise, which looks wrong even though the math is correct.
2
Count only working days (skip weekends).
=NETWORKDAYS(A2,B2) counts weekdays only between two dates, automatically excluding Saturdays and Sundays — useful for estimating actual crew-days on a job rather than raw calendar days.
3
Exclude holidays too.
List your holiday dates in a range (say H2:H10), then use =NETWORKDAYS(A2,B2,H2:H10) — the third argument excludes those dates from the working-day count as well.
4
Find a completion date from a start date + duration.
=WORKDAY(A2,15,H2:H10) answers "if we start on this date and the job takes 15 working days, what date do we finish?" — again skipping weekends and any holiday list you provide.
5
Track labor hours from clock-in/clock-out times.
With a start time in C2 and end time in D2, =D2-C2 gives elapsed time. Format the cell as [h]:mm (not the standard time format) so shifts longer than 24 hours in a weekly total still display correctly instead of resetting to zero.
For a live "days remaining" countdown that updates every time the sheet is opened, use =DeadlineCell-TODAY(). Since TODAY() recalculates automatically, you never have to manually update it — just don't use it in a sheet you need frozen for historical record-keeping, since older printouts will show a different countdown than the live file.

Combined, these five formulas cover the vast majority of date math a project actually needs — from bid deadlines to crew scheduling to payroll hours — without needing a separate scheduling tool for the calculation itself.

Worked example: a 15-working-day task

A task starts on Monday, October 5, 2026. Columbus Day (October 12) is on your holiday list in H2.

QuestionFormulaResult
Finish date, no holidays=WORKDAY(DATE(2026,10,5),15)Mon Oct 26
Finish date, with the holiday=WORKDAY(DATE(2026,10,5),15,H2)Tue Oct 27
Working days from Oct 5 to Oct 26=NETWORKDAYS(DATE(2026,10,5),DATE(2026,10,26))16 (both ends counted)

Notice the difference between the two functions: WORKDAY does not count the start day, while NETWORKDAYS counts both the start and end days. This is the most common reason two schedules disagree by one day.

Timesheet calculations

  • Shift length: a shift from 6:30 AM to 3:00 PM with =D2-C2 equals 8.5 hours when the result is formatted as [h]:mm.
  • Decimal hours for payroll: multiply by 24, as in =(D2-C2)*24, which gives 8.5.
  • Overnight shifts: if the end time is after midnight, use =MOD(D2-C2,1) to avoid a negative result.
  • Unpaid breaks: subtract TIME(0,30,0) for a 30-minute lunch.

Common mistakes

  • Result shows as a date. Subtracting dates returns a number, but Excel may format it as a date. Change the cell format to Number or General.
  • Dates stored as text. If a date is left-aligned and functions fail, it is probably text. Re-enter it, or convert with DATEVALUE.
  • Totals over 24 hours reset to zero. Use the [h]:mm format, with brackets, so hours keep accumulating.

Frequently asked questions

Can I define a different weekend, for example Friday and Saturday?

Yes. Use NETWORKDAYS.INTL and WORKDAY.INTL, which accept a weekend code or a seven-character string such as "0000110".

Why does my finish date land on a weekend?

Plain addition (start + 15) counts calendar days. Use WORKDAY if you want weekends skipped.

Back to all tutorials →
← Back to all tutorials