Calculate time difference but ignore dates

Hi JupiterLight,

You have discovered the "Crossing Midnight" feature of Numbers. Welcome to the club!

Numbers will silently insert a Date (the day when a Time is entered).

Reducing your table to Sunday start and Sunday stop (and I did not use Merge Cells in Row 1; they can cause problems):

Here is a Numbers table with Date & Time both displayed in Columns B and C. No need to display the date. I chose to display it for clarity.

Columns A, B, and C are entries. Type the time and Numbers will fill in the current date (even if the cell does not display the date).

Formula in D2 (and Fill Down)

=IF(OR(B2="",C2=""),"Start & Stop?","OK")

If B or C is blank "" (NULL, empty), then return the text "Start & Stop?", else return the text "OK"

The red error triangle in E7 is another alert (Allison has not entered a Stop time)

Formula in E2 =C2−B2−"0.5h" (half hour lunch break?)

Conditional Highlighting in Column E

Formula in F2 =IFERROR(IF(DUR2DAYS(C2−B2)>1,C2−B2−"0.5h"−"1d",C2−B2−"0.5h"),"0h")

The Bold part does the work in correcting the actual work time by subtracting "0.5h" (lunch break) and "1d" (1 day) if the Stop time is entered a day late.

The IFERROR function wrapped around the Bold part enters "0h" if the timesheet is not complete. Note that "0h" is aligned left because it is a Text value.

Formula in G2 =DUR2HOURS(F2)

That converts a Duration to a number for you to use in your payroll calculation.

Regards,

Ian.

Hide the columns that are not needed in your timesheet.

JupiterLight wrote:

I want the formula to ignore the calendar date and only consider the time difference.

There must be a way to do this, right?

Yes, if you want to work with "just times" and not let Numbers automatically insert dates all you have to do is enter the times as Text rather than Automatic or Date & Time. I find the easiest way to do this is just type ' before the time when entering the time.

For example 7:45 AM would be entered as '7:45 AM. The ' disappears after you've made the entry.

Numbers will happily perform math on the times entered as Text. There usually is no need to change your formula.

Or if you want the number of hours as a number you can easily do something like this:

The times here were entered as Text and are left-aligned by default.

SG

Yellowbox wrote:

Hi SG,

I was thinking back to https://discussions.apple.com/thread/5473882

What has been GAINED in the Numbers 3.0 upgrade?

The good old days!

Regards,

Ian.

Yes, there was more to complain about then, wasn't there!🙂

Is grizzle or whinge a correct synonym for that in Australia?

SG

Is grizzle or whinge a correct synonym for that in Australia?

We had some whingers, but you showed leadership by encouraging a move forward from Numbers '09 to Numbers 3.0 back in late 2013. And you continue to lead with Numbers 5. Thank you!

Regards,

Ian.

Hi SG,

Dates and/or Times entered as text are recognised in calculations.

A good example of what we have GAINED in Numbers 5.x 🙂.

Regards,

Ian.

Yes, some things do actually get better.🙂

SG

Hi SG,

I was thinking back to https://discussions.apple.com/thread/5473882

What has been GAINED in the Numbers 3.0 upgrade?

The good old days!

Regards,

Ian.