Working on a time sheet I need to deduct cell(a1)- start time from cell(b1) -
end time plus another 30 minutes - lunch time (TIME(0,30,0)? But I need
nested if statements also as in:

if (endTime - startTime <4hrs, endTime - startTime,if(endTime -
startTime4hrs, (endTime - startTime) - 30mins, if((endTime - startTime) -
30mins8hrs, 8hrs)))

This might not appear to make sense but it does because surplus hours will
be in another cell to be calculated at a different rate.

I have been doing this just with numbers, but it gets awkward taking away
the 30 minutes as in ((16.30 - 08.00)-0.30) = 8, (correct),but, ((16:30 -
08.30)-0.30) = 7.7 (because it's time represented by a number it should be
7.5, or if time 7.30)

There must be a simple way round this isn't there?

Crazy Lady,

Working with times can become involved but for your example with Start time
in B2 and finish time in C2 try:

=IF(C2="","",MIN(C2-B2-0.5/24*(C2-B24/24),8/24))

You may find it better to have a column for luch time and sutract that so
that it is clear when a meal break is included.

note that dividing the number of hours 0.5, 4 or 8 by 24 gives the decimal
equivalent to the time.

--
HTH

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


with @tiscali.co.uk


"Crazy Lady" wrote in message
...
Working on a time sheet I need to deduct cell(a1)- start time from
cell(b1) -
end time plus another 30 minutes - lunch time (TIME(0,30,0)? But I need
nested if statements also as in:

if (endTime - startTime <4hrs, endTime - startTime,if(endTime -
startTime4hrs, (endTime - startTime) - 30mins, if((endTime - startTime) -
30mins8hrs, 8hrs)))

This might not appear to make sense but it does because surplus hours will
be in another cell to be calculated at a different rate.

I have been doing this just with numbers, but it gets awkward taking away
the 30 minutes as in ((16.30 - 08.00)-0.30) = 8, (correct),but, ((16:30 -
08.30)-0.30) = 7.7 (because it's time represented by a number it should be
7.5, or if time 7.30)

There must be a simple way round this isn't there?

Thanks Sandy, I'll try this, but I'll have to find a new start to my formula
because I forgot to mention that it starts with "if istext(cell)" because
sometimes the cell might contain 'hol' or 'sick'. I may be able to work it
with "if isnumber".

Cheers

"Sandy Mann" wrote:

Crazy Lady,

Working with times can become involved but for your example with Start time
in B2 and finish time in C2 try:

=IF(C2="","",MIN(C2-B2-0.5/24*(C2-B24/24),8/24))

You may find it better to have a column for luch time and sutract that so
that it is clear when a meal break is included.

note that dividing the number of hours 0.5, 4 or 8 by 24 gives the decimal
equivalent to the time.

--
HTH

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


with @tiscali.co.uk


"Crazy Lady" wrote in message
...
Working on a time sheet I need to deduct cell(a1)- start time from
cell(b1) -
end time plus another 30 minutes - lunch time (TIME(0,30,0)? But I need
nested if statements also as in:

if (endTime - startTime <4hrs, endTime - startTime,if(endTime -
startTime4hrs, (endTime - startTime) - 30mins, if((endTime - startTime) -
30mins8hrs, 8hrs)))

This might not appear to make sense but it does because surplus hours will
be in another cell to be calculated at a different rate.

I have been doing this just with numbers, but it gets awkward taking away
the 30 minutes as in ((16.30 - 08.00)-0.30) = 8, (correct),but, ((16:30 -
08.30)-0.30) = 7.7 (because it's time represented by a number it should be
7.5, or if time 7.30)

There must be a simple way round this isn't there?

Don't use 16.30, it should be 16:30.

If you've got 16:30 in A1, 08:30 in A2, then =((A1 - A2) - TIME(0,30,0))
will give you 07:30. Divide it by 24 and format as General will give 7.5.
--
David Biddulph

"Crazy Lady" wrote in message
...
Working on a time sheet I need to deduct cell(a1)- start time from
cell(b1) -
end time plus another 30 minutes - lunch time (TIME(0,30,0)? But I need
nested if statements also as in:

if (endTime - startTime <4hrs, endTime - startTime,if(endTime -
startTime4hrs, (endTime - startTime) - 30mins, if((endTime - startTime) -
30mins8hrs, 8hrs)))

This might not appear to make sense but it does because surplus hours will
be in another cell to be calculated at a different rate.

I have been doing this just with numbers, but it gets awkward taking away
the 30 minutes as in ((16.30 - 08.00)-0.30) = 8, (correct),but, ((16:30 -
08.30)-0.30) = 7.7 (because it's time represented by a number it should be
7.5, or if time 7.30)

There must be a simple way round this isn't there?

Hi David,

I had tried that format and it partly worked except when I started nesting
the if statements (particularly the last one). However, I didn't know about
the divide by 24 and format as general. I'll try that, thanks

Sue

"David Biddulph" wrote:

Don't use 16.30, it should be 16:30.

If you've got 16:30 in A1, 08:30 in A2, then =((A1 - A2) - TIME(0,30,0))
will give you 07:30. Divide it by 24 and format as General will give 7.5.
--
David Biddulph

"Crazy Lady" wrote in message
...
Working on a time sheet I need to deduct cell(a1)- start time from
cell(b1) -
end time plus another 30 minutes - lunch time (TIME(0,30,0)? But I need
nested if statements also as in:

if (endTime - startTime <4hrs, endTime - startTime,if(endTime -
startTime4hrs, (endTime - startTime) - 30mins, if((endTime - startTime) -
30mins8hrs, 8hrs)))

This might not appear to make sense but it does because surplus hours will
be in another cell to be calculated at a different rate.

I have been doing this just with numbers, but it gets awkward taking away
the 30 minutes as in ((16.30 - 08.00)-0.30) = 8, (correct),but, ((16:30 -
08.30)-0.30) = 7.7 (because it's time represented by a number it should be
7.5, or if time 7.30)

There must be a simple way round this isn't there?