Microsoft Excel Formula "IF" to create a timesheet

[reagolly]

I used the following formula to create a timehseet. I work from 8:30 to 4:30
with an hour paid lunch from 11:30 to 12:30. This formula only gives me 7
hours. How can I change the formula to give me 8 hours?
=IF((((D13-C13)+(F13-E13))*24)>8,8,((D13-C13)+(F13-E13))*24)

 

[Fred Smith]

First you need to tell us what's in d13, c13, e13 and f13.

Regards,
Fred.

 

[Sandy Mann]

Being as your luch is paid time don't subtract it:

=IF((F13-C13)*24>8,8,(F13-C13)*24)

or perhaps:

=MIN((F13-C13)*24,8)

Although you may want to prevent the result showing until data is entered:

=IF(COUNT(C13:F13)<4,"",IF((13-C13)*24>8,8,(F13-C13)*24))

or:

=IF(COUNT(C13:F13)<4,"",MIN((F13-C13)*24,8))

--
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

 

[David Biddulph]

You can simplify your existing formula by changing
=IF((((D13-C13)+(F13-E13))*24)>8,8,((D13-C13)+(F13-E13))*24) to
=MIN(8,((D13-C13)+(F13-E13))*24)

The reason that you've got 7 hours instead of 8 is that you've not given
credit for the hour at lunch, which you say is paid. In the simplest form,
you can just delete the content of D13 and E13 (or otherwise just add 1 to
the second term in the MIN expression), but you may instead wish to define
how to treat the data if the lunch break is longer than, or shorter than, 1
hour.

If you want to subtract time for any excess of the lunch break beyond 1
hour, you may want =MIN(8,((F13-C13)-MAX(E13-D13-1/24,0))*24)