Time Calculation

[Sj]

I am trying to work out how to calculate the difference between times for
example my sheet is to work out the finishing times of staff, so if their set
time is 17:30 and they finish one night 17:15 and the next 17:45 i need a
formula that allows me to work out the difference but i'm getting stuck with
the negative bit.

 

[Roger Govier]

Hi

To display negative times you would need to switch to the 1904 Data system
Tools>Calculation>1904 Date

Be aware though, that if you have any dates already entered on the sheet
under the 1900 default system, then those dates will be out by just over 4
years.

 

[Sj]

Thanks
My cells look like this ( d3 is a set cell)
D3 e3 f3
18:00 18:15 23:45
17:45 18:15 00:30

the formula I have in f3 is this
=IF(E3>D3,D3+1-E3,D3-E3)
and in f4
=IF(D4>E4,E4+1-D4,E4-D4)

I want to join the two so that when I put a time in e3 it will work out the
difference either way.

 

[Sandy Mann]

Would:

=MAX(D3:E3)-MIN(D3:E3)

do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Roger Govier]

Hi

=MOD(E3-D3,1)
will give that result for either row.
or
=E3-D3+(D3>E3)

The result of 23:45 however, is wrong, if the times are on the same day.
It should be -00:15, but unless you use the a90m as advised previously, you
cannot display negative times.

 

[Sj]

Yes thanks, now only thing is is if the time is over is there a way it can be
displayed in red?

 

[Sandy Mann]

Format > Conditional Formatting > Condition 1 > Formula is:

=D3-E3<0

Select the Format button in the dialog box then Font and select Red as the
font colour.

or you may have meant =E3-D<0 as the condition.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Roger Govier]

but unless you use the a90m as advised previously

Don't know how I managed to type that, I meant to type

but unless you use the 1904 date system as advised previously