Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 1st 07, 06:25 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 1
Default 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   Report Post  
Old February 1st 07, 07:28 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 16
Default 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   Report Post  
Old February 1st 07, 07:34 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 210
Default 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   Report Post  
Old February 1st 07, 07:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 16
Default 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   Report Post  
Old February 1st 07, 07:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 210
Default 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"

  #7   Report Post  
Old February 1st 07, 07:55 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 16
Default Desperately needing furmula help

OK, so how would the formula know when to subtract the time because there are
plenty of entries that are made within the range of normal working hours? If
I made a blanket formula it whould have to know when to use the exclusions
and when not to. Any Ideas?

"galimi" wrote:

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"

  #8   Report Post  
Old February 2nd 07, 07:35 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,339
Default Desperately needing furmula help

Fred,
Try this:

In say cell C1 , formatted as [h]:mm, put

=(9+(MOD(B1,1)-MOD(A1,1))*24+(NETWORKDAYS(A1,B1)-2)*9)/24

Where A1=Start date/time
B1=Finish date/time

My assumption is that start/finish times must be in the 0800 to 1700 window.

You will need to have Analysis Toolpak add-in activated
(Tools==Add-ins==Analysis Toolpak) to use the NETWORKDAYS function.

NETWORKDAYS allows you to add holidays into the calculation if this is
required.

For your example I get a result of 6 hours and 6 minutes.

HTH

"Fred "Djinn" Holstings" wrote:

OK, so how would the formula know when to subtract the time because there are
plenty of entries that are made within the range of normal working hours? If
I made a blanket formula it whould have to know when to use the exclusions
and when not to. Any Ideas?

"galimi" wrote:

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"

  #9   Report Post  
Old February 2nd 07, 07:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 16
Default Desperately needing furmula help

Holy Cow It Works!!!!!! So that networkdays takes into account weekends
non-work hours and holidays??? Is that right?? Also, what If I want it to set
the work hours to start at 7:30 am and end at 17:00??

THANK YOU SOOOO MUCH!!!!!!!!!!
My Goodness How can I ever repay you for this??

Seriously Thankful!!
Fred



"Toppers" wrote:

Fred,
Try this:

In say cell C1 , formatted as [h]:mm, put

=(9+(MOD(B1,1)-MOD(A1,1))*24+(NETWORKDAYS(A1,B1)-2)*9)/24

Where A1=Start date/time
B1=Finish date/time

My assumption is that start/finish times must be in the 0800 to 1700 window.

You will need to have Analysis Toolpak add-in activated
(Tools==Add-ins==Analysis Toolpak) to use the NETWORKDAYS function.

NETWORKDAYS allows you to add holidays into the calculation if this is
required.

For your example I get a result of 6 hours and 6 minutes.

HTH

"Fred "Djinn" Holstings" wrote:

OK, so how would the formula know when to subtract the time because there are
plenty of entries that are made within the range of normal working hours? If
I made a blanket formula it whould have to know when to use the exclusions
and when not to. Any Ideas?

"galimi" wrote:

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"

  #10   Report Post  
Old February 2nd 07, 08:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,339
Default Desperately needing furmula help

Fred,

Let me break down the formula:

=(9+(MOD(B1,1)-MOD(A1,1))*24+(NETWORKDAYS(A1,B1)-2)*9)/24

The constant 9 reflects the length of the working day i.e 0800 to 1700;
hence if you want start at 07:30 then the working day would be 9.5 (decimal)
hours. You could assign this value to a cell and replace the 9 with cell
address.


HTH



"Fred "Djinn" Holstings" wrote:

Holy Cow It Works!!!!!! So that networkdays takes into account weekends
non-work hours and holidays??? Is that right?? Also, what If I want it to set
the work hours to start at 7:30 am and end at 17:00??

THANK YOU SOOOO MUCH!!!!!!!!!!
My Goodness How can I ever repay you for this??

Seriously Thankful!!
Fred



"Toppers" wrote:

Fred,
Try this:

In say cell C1 , formatted as [h]:mm, put

=(9+(MOD(B1,1)-MOD(A1,1))*24+(NETWORKDAYS(A1,B1)-2)*9)/24

Where A1=Start date/time
B1=Finish date/time

My assumption is that start/finish times must be in the 0800 to 1700 window.

You will need to have Analysis Toolpak add-in activated
(Tools==Add-ins==Analysis Toolpak) to use the NETWORKDAYS function.

NETWORKDAYS allows you to add holidays into the calculation if this is
required.

For your example I get a result of 6 hours and 6 minutes.

HTH

"Fred "Djinn" Holstings" wrote:

OK, so how would the formula know when to subtract the time because there are
plenty of entries that are made within the range of normal working hours? If
I made a blanket formula it whould have to know when to use the exclusions
and when not to. Any Ideas?

"galimi" wrote:

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
NEEDING FORMULA Please laurie g Excel Worksheet Functions 3 September 12th 06 03:16 AM
Furmula Dependent Cell References ned Excel Discussion (Misc queries) 2 May 4th 06 05:03 PM
How can I use replace(alt+H) for mutiple items needing replace Gery Excel Worksheet Functions 1 June 15th 05 05:51 PM
New user needing help with coming up with a formula Igniter New Users to Excel 2 June 2nd 05 07:10 PM
Would be nice to download templates w/o needing activeX Dislike activeX Excel Worksheet Functions 0 May 2nd 05 07:49 PM


All times are GMT +1. The time now is 09:08 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017