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.

Hi

To display negative times you would need to switch to the 1904 Data system
ToolsCalculation1904 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.
--
Regards
Roger Govier

"Sj" wrote in message
...
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.

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(E3D3,D3+1-E3,D3-E3)
and in f4
=IF(D4E4,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.




"Roger Govier" wrote:

Hi

To display negative times you would need to switch to the 1904 Data system
ToolsCalculation1904 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.
--
Regards
Roger Govier

"Sj" wrote in message
...
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.

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


Replace @mailinator.com with @tiscali.co.uk


"Sj" wrote in message
...
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(E3D3,D3+1-E3,D3-E3)
and in f4
=IF(D4E4,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.




"Roger Govier" wrote:

Hi

To display negative times you would need to switch to the 1904 Data
system
ToolsCalculation1904 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.
--
Regards
Roger Govier

"Sj" wrote in message
...
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.

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

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Sj" wrote in message
...
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(E3D3,D3+1-E3,D3-E3)
and in f4
=IF(D4E4,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.




"Roger Govier" wrote:

Hi

To display negative times you would need to switch to the 1904 Data
system
ToolsCalculation1904 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.
--
Regards
Roger Govier

"Sj" wrote in message
...
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.

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


Replace @mailinator.com with @tiscali.co.uk


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

"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Sj" wrote in message
...
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(E3D3,D3+1-E3,D3-E3)
and in f4
=IF(D4E4,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.




"Roger Govier" wrote:

Hi

To display negative times you would need to switch to the 1904 Data
system
ToolsCalculation1904 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.
--
Regards
Roger Govier

"Sj" wrote in message
...
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.

Hi

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

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.

--
Regards
Roger Govier

"Sj" wrote in message
...
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(E3D3,D3+1-E3,D3-E3)
and in f4
=IF(D4E4,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.




"Roger Govier" wrote:

Hi

To display negative times you would need to switch to the 1904 Data
system
ToolsCalculation1904 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.
--
Regards
Roger Govier

"Sj" wrote in message
...
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.

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

--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

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

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.

--
Regards
Roger Govier

"Sj" wrote in message
...
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(E3D3,D3+1-E3,D3-E3)
and in f4
=IF(D4E4,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.




"Roger Govier" wrote:

Hi

To display negative times you would need to switch to the 1904 Data
system
ToolsCalculation1904 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.
--
Regards
Roger Govier

"Sj" wrote in message
...
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.