| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Desperately needing furmula help
Hi,
I need to figure out how to add time while taking into consideration that there are weekends and limited work hours. Here's what I have... Row E13 Row F13 Row K13 01/26/2007 3:41 PM 01/29/2007 12:47 PM 2 days 21 hours 06 minutes -- The formula for Row K13... =IF(F13<FALSE,F13-E13,"") With respect to the length of time, the 27th and 28th were weekend days and the normal work hours are from 8 to 5. How do I get the formula to subtract the amount of time that the weekends and off-work hours constitute so that I come up with a true and accurate amount of time that the user had expended? Thanks So Much, Fred "Djinn" |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Desperately needing furmula help
Sweet, I can see how that will work as a constant for subtract the time
between 5 pm and 8AM but how would I use it as a constant to account for every weekend and holidays? Thanks so much, Fred "galimi" wrote: Fred, I would also build an exclusion time period. In your example, 1/26/2007 5 PM and 1/29/2007 8 AM. When subtracting, you will get 2 days and 15 hours, which you can subtract from your formula for the correct time. -- http://HelpExcel.com "Fred "Djinn" Holstings" wrote: Hi, I need to figure out how to add time while taking into consideration that there are weekends and limited work hours. Here's what I have... Row E13 Row F13 Row K13 01/26/2007 3:41 PM 01/29/2007 12:47 PM 2 days 21 hours 06 minutes -- The formula for Row K13... =IF(F13<FALSE,F13-E13,"") With respect to the length of time, the 27th and 28th were weekend days and the normal work hours are from 8 to 5. How do I get the formula to subtract the amount of time that the weekends and off-work hours constitute so that I come up with a true and accurate amount of time that the user had expended? Thanks So Much, Fred "Djinn" |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Desperately needing furmula help
It will work the same way. Just create a string of end & start dates, find
the difference, sum of the total and subtract THAT total from your number in the example. For example, for every day of the week, you could have the date & the ending time, 5 PM. The cell below is would be the next day and the start time. Subtract the difference and you have the portion you want to exclude. You can do this for every day in between the range and for the weekend, it would start at 5 PM on Friday evening and end at 8 AM on Monday morning. -- http://HelpExcel.com "Fred "Djinn" Holstings" wrote: Sweet, I can see how that will work as a constant for subtract the time between 5 pm and 8AM but how would I use it as a constant to account for every weekend and holidays? Thanks so much, Fred "galimi" wrote: Fred, I would also build an exclusion time period. In your example, 1/26/2007 5 PM and 1/29/2007 8 AM. When subtracting, you will get 2 days and 15 hours, which you can subtract from your formula for the correct time. -- http://HelpExcel.com "Fred "Djinn" Holstings" wrote: Hi, I need to figure out how to add time while taking into consideration that there are weekends and limited work hours. Here's what I have... Row E13 Row F13 Row K13 01/26/2007 3:41 PM 01/29/2007 12:47 PM 2 days 21 hours 06 minutes -- The formula for Row K13... =IF(F13<FALSE,F13-E13,"") With respect to the length of time, the 27th and 28th were weekend days and the normal work hours are from 8 to 5. How do I get the formula to subtract the amount of time that the weekends and off-work hours constitute so that I come up with a true and accurate amount of time that the user had expended? Thanks So Much, Fred "Djinn" |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Desperately needing furmula help
So your saying that I should have it set up to where every day and weekend
has it's own exclusions set up? Or am I just getting confused? "galimi" wrote: It will work the same way. Just create a string of end & start dates, find the difference, sum of the total and subtract THAT total from your number in the example. For example, for every day of the week, you could have the date & the ending time, 5 PM. The cell below is would be the next day and the start time. Subtract the difference and you have the portion you want to exclude. You can do this for every day in between the range and for the weekend, it would start at 5 PM on Friday evening and end at 8 AM on Monday morning. -- http://HelpExcel.com "Fred "Djinn" Holstings" wrote: Sweet, I can see how that will work as a constant for subtract the time between 5 pm and 8AM but how would I use it as a constant to account for every weekend and holidays? Thanks so much, Fred "galimi" wrote: Fred, I would also build an exclusion time period. In your example, 1/26/2007 5 PM and 1/29/2007 8 AM. When subtracting, you will get 2 days and 15 hours, which you can subtract from your formula for the correct time. -- http://HelpExcel.com "Fred "Djinn" Holstings" wrote: Hi, I need to figure out how to add time while taking into consideration that there are weekends and limited work hours. Here's what I have... Row E13 Row F13 Row K13 01/26/2007 3:41 PM 01/29/2007 12:47 PM 2 days 21 hours 06 minutes -- The formula for Row K13... =IF(F13<FALSE,F13-E13,"") With respect to the length of time, the 27th and 28th were weekend days and the normal work hours are from 8 to 5. How do I get the formula to subtract the amount of time that the weekends and off-work hours constitute so that I come up with a true and accurate amount of time that the user had expended? Thanks So Much, Fred "Djinn" |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Desperately needing furmula help
That is precisely what I'm saying. You could create a UDF out of this, but I
believe it would just easier to have the person enter a start and end time to make your calculating easier. -- http://HelpExcel.com "Fred "Djinn" Holstings" wrote: So your saying that I should have it set up to where every day and weekend has it's own exclusions set up? Or am I just getting confused? "galimi" wrote: It will work the same way. Just create a string of end & start dates, find the difference, sum of the total and subtract THAT total from your number in the example. For example, for every day of the week, you could have the date & the ending time, 5 PM. The cell below is would be the next day and the start time. Subtract the difference and you have the portion you want to exclude. You can do this for every day in between the range and for the weekend, it would start at 5 PM on Friday evening and end at 8 AM on Monday morning. -- http://HelpExcel.com "Fred "Djinn" Holstings" wrote: Sweet, I can see how that will work as a constant for subtract the time between 5 pm and 8AM but how would I use it as a constant to account for every weekend and holidays? Thanks so much, Fred "galimi" wrote: Fred, I would also build an exclusion time period. In your example, 1/26/2007 5 PM and 1/29/2007 8 AM. When subtracting, you will get 2 days and 15 hours, which you can subtract from your formula for the correct time. -- http://HelpExcel.com "Fred "Djinn" Holstings" wrote: Hi, I need to figure out how to add time while taking into consideration that there are weekends and limited work hours. Here's what I have... Row E13 Row F13 Row K13 01/26/2007 3:41 PM 01/29/2007 12:47 PM 2 days 21 hours 06 minutes -- The formula for Row K13... =IF(F13<FALSE,F13-E13,"") With respect to the length of time, the 27th and 28th were weekend days and the normal work hours are from 8 to 5. How do I get the formula to subtract the amount of time that the weekends and off-work hours constitute so that I come up with a true and accurate amount of time that the user had expended? Thanks So Much, Fred "Djinn" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| NEEDING FORMULA Please | Excel Worksheet Functions | |||
| Furmula Dependent Cell References | Excel Discussion (Misc queries) | |||
| How can I use replace(alt+H) for mutiple items needing replace | Excel Worksheet Functions | |||
| New user needing help with coming up with a formula | New Users to Excel | |||
| Would be nice to download templates w/o needing activeX | Excel Worksheet Functions | |||
Hybrid Mode
