I had a recent query regarding checking time in a column that had both date and time. There is an easy way to extract time from a date-time combination.
In Excel dates are whole numbers and time is a fraction. So 6am is 0.25, noon is 0.5 and 6pm is 0.75.
To extract a fraction from a number the easiest formula is the MOD function.
If A1 had the number, we can use
To extract the fraction.
The MOD function returns the remainder after dividing by a number, in our case 1. This means it returns the fraction.
The table below has a list of date-time entries.
We can extract the time using
In cell B2 and copy it down.
You will need to apply a time format to see the result.
The query was to identify times around work hours. Between 9 and 5 was work and outside was deemed home.
The formula in cell C2 in the image below is
Between rows 9 and 10 you can see the change.
Cells F1 and I1 are entered as times.
To enter a time, use the colon after the number. Entering 9: will enter 9am and 17: enters 5 pm.