How to check if a time is before a cutoff time in Excel
Formula (Excel and Google Sheets)
=IF(MOD(B2,1)<$F$1,"Same day","Next day")Take the time of day with MOD(timestamp,1) and compare it with the cutoff: =IF(MOD(B2,1)<$F$1,"Same day","Next day").
- Result on the example
- one result per row in
C2, filled down toC7 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.
How it works
A date-time is a whole number of days plus a fraction for the time: noon is 0.5. MOD(B2,1) keeps only the fraction, the time of day, which is compared with the cutoff, 2:00 PM. W-205 at 1:12 PM ships the same day; W-202 at 2:24 PM waits.
=IF(MOD(B2,1)<$F$1,"Same day","Next day")Result"Same day"
B210/28/2026 9:00:00The value in B2.
MOD(B2,1)0.375MOD: Remainder after division.- number
B210/28/2026 9:00:00 - divisor
1
- number
$F$12:00 PMThe value in F1.
MOD(B2,1)<$F$1TRUEThe < operator tests less than.
IF(MOD(B2,1)<$F$1,"Same day","Next day")"Same day"IF: Returns one value if a test is true, another if false.- logical_test
MOD(B2,1)<$F$1TRUE - value_if_true
"Same day" - value_if_false
"Next day"not used
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(MOD(B2,1)<$F$1,"Same day","Next day"). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(B2-INT(B2)<$F$1,"Same day","Next day") | Same day, Next day, Same day… | INT strips the time, so B2-INT(B2) is the time of day. |
| =IF(TEXT(B2,"hh:mm")<"14:00","Same day","Next day") | Same day, Next day, Same day… | Compares the time as text. It works because zero-padded 24-hour times sort in order, but a cutoff in a cell is easier to change. |
Common mistakes
- Comparing the whole timestamp with the cutoff (
B2<$F$1) compares a date with a time and is never TRUE. - Typing "14:00" in quotes in the comparison compares text with a number. Keep the cutoff in a cell, or use TIME(14,0,0).
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Check if a time is before a cutoff time
C2, show "Same day" when the order was placed before the cutoff time in F1, otherwise "Next day". Fill down to C7.Questions
How do I check if a time is between two times?
=AND(MOD(B2,1)>=TIME(9,0,0),MOD(B2,1)<TIME(17,0,0)).
How do I return the ship date instead?
=INT(B2)+(MOD(B2,1)>=$F$1), formatted as a date, adds a day for late orders.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.