time and if statements
 

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?

time and if statements
 

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?

time and if statements
 

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?

time and if statements
 

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?

time and if statements
 

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?