Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-29-2012, 04:19 PM
JAMS JAMS is offline Formula help request Mac OS X Formula help request Office for Mac 2011
Novice
Formula help request
 
Join Date: Mar 2012
Posts: 1
JAMS is on a distinguished road
Default 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
File Type: jpg Sample.jpg (16.5 KB, 13 views)
Reply With Quote
  #2  
Old 03-30-2012, 12:42 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Formula help request Windows 7 64bit Formula help request Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 04-06-2012, 10:14 PM
Karthik Bhat Karthik Bhat is offline Formula help request Windows Vista Formula help request Office 2007
Novice
 
Join Date: Apr 2012
Posts: 1
Karthik Bhat is on a distinguished road
Default

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
File Type: xlsx Formula_7Apr.xlsx (8.7 KB, 6 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Meeting Request Time Wrong aimav Outlook 0 03-26-2012 08:14 AM
Formula help request 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft