#1
|
|||
|
|||
Formula help request
Urgently need help modifying this formula:
=IF(F13="NO",((D13+E13)-(B13+C13))*24,0) Is it possible to have Excel subtract 1.5 hours from the start time and add .5 hours to the end time? A pilot duties on at 0800 for instance, and off at 1600. His per diem starts at 0630 and ends at 1630, so he works 8 hours but gets paid a per diem of 10 hours for meals. That example is straight forward but I am trying to avoid math errors when he duties on at 0817 for instance, and has to calculate 1 hour and thirty minutes prior to that time to enter it manually in his per diem claim spreadsheet. Is there a way to modify the formula to do that? I would like to be able to enter 0800 and have 0630 populate that field or have the calculation of total hours total to 10 instead of 8. Your assistance is appreciated. |
#2
|
||||
|
||||
What does your formula represent ? ( always easier when one can see a sample sheet)
To deduce 1.5 hours from start time : start_time-1.5/24 To add .5 hr = end_time +.5/24 When start and end time are entered in sexagesimal format
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Hi
As you have not given what each field represents I am making following assumption: B= Start time C= 1.5 hrs D= End time E= .5 hrs You can modify your formulas as follows: =HOUR(IF(F13="NO",((D13+E13)-(B13-C13)),0))*24 Make sure the start time and end time in column B and D contain both time and date (something like 0/1/1900 12:31:00 PM) else excel will not be able to calculate the hours correctly. If you do not want to see this format on your screen just change the format to HH:MM. You may also use following formula: =HOUR(IF(F13="NO",(D13-B13+TIME(2,0,0)),0))*24 With this formula you just need start time and end time. Make sure that the column in which you enter this formula to calculate per diem is formatted as a number. Thx KB |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Meeting Request Time Wrong | aimav | Outlook | 0 | 03-26-2012 08:14 AM |
Page numbering special request | Ulodesk | Word | 1 | 12-07-2011 05:05 PM |
Forms: A very basic question/request | Coli | Outlook | 0 | 08-24-2011 11:14 AM |
Outlook Calendar request | steve42399 | Outlook | 2 | 04-12-2011 09:35 AM |
Meeting Request - Another Organizer | coolpeter86 | Outlook | 0 | 12-12-2010 11:58 PM |