Build dates and calculate elapsed hours safely

Construct numeric date-times from explicit components and separate stored values from their display formats.

Before you start

Excel 2016, 2019, 2021, 2024, and Microsoft 365 for Windows desktop. This exercise uses the 1900 date system and same-day intervals. English formulas use commas; some locales require semicolons. Time is an estimate.

Useful first: Format numbers without confusing display and value · Build your first formulas from cell references · Copy formulas while keeping shared inputs fixed

By the end, you can…

Create unambiguous dates with DATE and clock times with TIME.
Calculate elapsed decimal hours from numeric date-times.
Distinguish display formatting, date systems, and time-of-day assumptions.

YOUR PRACTICE FILE

Small data. A result you can check.

Synthetic integer components only. Paste at A1 and build the H:L formulas; the sample does not contain executable formulas.

Download practice data

View the raw practice data

Year	Month	Day	StartHour	StartMinute	FinishHour	FinishMinute
2026	9	14	9	30	17	15
2026	9	15	8	0	12	30

01Use components instead of ambiguous date text

Paste the sample at A1:G3. The first row describes September 14, 2026, from 09:30 to 17:15; the second describes September 15 from 08:00 to 12:30. Integer year, month, day, hour, and minute fields avoid guessing whether a slash-formatted date means month/day or day/month.

Add Date, Start, Finish, Hours, and Duration in H1:L1. In H2 enter =DATE(A2,B2,C2), then fill to H3. Format H2:H3 as yyyy-mm-dd. A numeric date may initially display as a serial number; changing the format changes its appearance, not its underlying value.

Column	Meaning
A:C	Year, month, day
D:E	Start hour and minute
F:G	Finish hour and minute
H:L	Calculated date, start, finish, hours, duration

02Attach each clock time to its date

In I2 enter =H2+TIME(D2,E2,0). In J2 enter =H2+TIME(F2,G2,0). Fill both formulas to row 3, then display I2:J3 as yyyy-mm-dd hh:mm. The first row should show 2026-09-14 09:30 and 2026-09-14 17:15.

TIME returns a fraction of a day: noon is 0.5. It also normalizes overflowing components, so TIME(27,0,0) represents 03:00 rather than a 27-hour duration. Likewise, DATE can roll an out-of-range day into another month. Constructing a date is not the same as validating its source components.

03Choose hours or a duration display

Enter the formula below in K2 and fill down. Because subtraction returns days, multiplying by 24 converts the difference to decimal hours. The results are 7.75 and 4.5. A value of 7.75 hours means seven hours and forty-five minutes, not seven hours and seventy-five minutes.

In L2 enter =J2-I2, fill through L3, and use custom format [h]:mm. The durations display as 7:45 and 4:30. Bracketed hours are useful when totals exceed 24 hours. Do not change the workbook date system to fix a display problem; serials copied between 1900 and 1904 systems need an explicit conversion decision.

=(J2-I2)*24

Enter in K2 and format as a number. L2 uses the unscaled day difference for a duration format.

PUT IT INTO PRACTICE

Extend the first interval by thirty minutes without editing the calculated result directly.

Check the original controls: K2 is 7.75, K3 is 4.5, and =SUM(K2:K3) in N2 is 12.25.
Change G2, the first finish minute, from 15 to 45; leave the finish hour at 17.
Confirm J2, K2, L2, and N2 update together, then restore G2 to 15.

Show the worked answer

After the change, J2 shows 2026-09-14 17:45, K2 is 8.25, L2 displays 8:15, and N2 is 12.75.

Restoring G2 to 15 restores the original 12.25-hour total.

Check your work

=ISNUMBER(I2) and =ISNUMBER(J2) both return TRUE.
The dates remain September 14 and 15; no locale-dependent date string was parsed.
The exercise describes local clock values without a time-zone or daylight-saving calculation.

CHECK YOUR UNDERSTANDING

One question before you move on.

A difference of 0.5 between two numeric Excel date-times represents what?Half a minute.Twelve hours.Fifty hours.

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.

NEXT LESSONAnswer the same business question with a sum and a count

Keep the skill close.

FIELD GUIDEExcel Date Serial Numbers and Time Fractions Explained FIELD GUIDECSV Dates Have Day and Month Swapped: Import with the Right Locale FIELD GUIDEExcel SUMIFS Date Range: Include the Whole End Date, Even with Times WORKSPACE TOOLCSV import checker

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.