Calculating Time With NUMBERS

Here is one way:

In cell B4:

=B5−DURATION(,,1,30)

More on the DURATION function here.

You can also use this:

=B5−"1h 30m"

SG

C2-B2-D2

C2 and B2 contain Date/Time values.

The result of C2-D2 is a Duration.

If D2 is also a Date/Time value, it cannot be subtracted from that Duration,

Format column D as Duration, reenter 1:00 as 1h (and 00:30 as 0h 30m), and your formula should return a correct result.

Regards,

Barry

I have this question based around time calculations:

I'm trying to migrate from EXCEL to Numbers on my iphone & I want to do calculations like so:
cell B2 (start time) = 8:30 cell C2 (finish time) = 17:30 cell D2 (lunch) = 0:30 or 1:00 or 1:30.

I've tried E2 = C2 - B2 - D2 .... but it gives me an error saying I cannot subtract a time from a number! ..

All the cells are in TIME format .....

I can subtract the start time from the finish time, but that won't take into account the lunch period ... never mind setting up a spreadsheet to accommodate a week's worth of data ... & expanding it or the year! The attached image shows my two-week cycle of time recording that I'm trying to migrate from Excel to Numbers!

The "first full line" shows start time of 8:15, a finish time of 17:30 & a lunchtime or 1:00, the duration fo the day is 8:15.

The next day has a shorter lunchtime so the duration is 8:45, but the cumulative hours are now 17:00 etc. & so on

Any advice?

Thanks.

Hi Sirecks

You wrote: "What I'm trying to accomplish is to input a time value with no date"

While you can enter the time part by itself, and omit the date part, what is contained in the cell is always a Date & Time value, specifying a particular instant in time. If you enter only the Time part, Numbers sets the Date part to the date on which the entry was created. If you enter only the Date part, Numbers sets the time part to 00:00:00 (midnight, at the beginning of that day).

"I've formatted the cells under Date & Time to have no date"

More accurately, you've "formatted the cells under Date & Time to show no date." Formatting a value changes only what is displayed; the underlying value is not changed by formatting.

The cell where you entered "14:00" still contains, but does not display, the date on which that entry was made.

The cell containing the result of B4 - "1h 30m" displays 12:30, but also contains the same date as the cell displaying 14:00.

Regards,

Barry