Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula "IF" to create a timesheet
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) -- reagolly |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula "IF" to create a timesheet
First you need to tell us what's in d13, c13, e13 and f13.
Regards, Fred. "reagolly" wrote in message ... 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) -- reagolly |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula "IF" to create a timesheet
Being as your luch is paid time don't subtract it:
=IF((F13-C13)*248,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)*248,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 Replace @mailinator.com with @tiscali.co.uk "reagolly" wrote in message ... 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) -- reagolly |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Microsoft Excel Formula "IF" to create a timesheet
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) -- David Biddulph "reagolly" wrote in message ... 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) -- reagolly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How to create a scatter chart with 2 "X" values with common "Y"s | Charts and Charting in Excel | |||
cannot use "Create List" and "Share Workbook" same time | Excel Discussion (Misc queries) | |||
create links to check boxes marked "good" fair"and "bad" | Excel Worksheet Functions | |||
how to create the formula "cumsum" in excel | Excel Worksheet Functions |