Microsoft Office Forums Formula help request
 Register FAQ Search Today's Posts Mark Forums Read

#1
03-29-2012, 04:19 PM
 JAMS Mac OS X Office for Mac 2011 Novice Join Date: Mar 2012 Posts: 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.
Attached Images
 Sample.jpg (16.5 KB, 7 views)
#2
03-30-2012, 12:42 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,432

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
#3
04-06-2012, 10:14 PM
 Karthik Bhat Windows Vista Office 2007 Novice Join Date: Apr 2012 Posts: 1

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
Attached Files
 Formula_7Apr.xlsx (8.7 KB, 0 views)

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post aimav Outlook 0 03-26-2012 08:14 AM Ulodesk Word 1 12-07-2011 05:05 PM Coli Outlook 0 08-24-2011 11:14 AM steve42399 Outlook 2 04-12-2011 09:35 AM coolpeter86 Outlook 0 12-12-2010 11:58 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 01:04 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top