Adding Business hours

**naven_sg** · 08-23-2011, 05:54 PM

Hello All,

I need to add 26 Business hours(this changes from time to time) to a particular Date..folowing are the few Date Examples:

Date
8/19/2011 19:30
8/19/2011 6:30
8/19/2011 10:30
8/20/2011 5:30
8/21/2011 10:30
8/21/2011 17:30
8/22/2011 6:55

Condition 1: Business hours is Monday to Friday 7:00 to 19:00
Condition 2: Some time i can see the date out of business hours, example 6:30...Technically it should calculate from 7:00...another example : 19:30...it should calcuate from next day 7:00.
Condition 3: If there is any date for Saturday (or) Sundary...irrespective at what time it starting...Calulation should start from Monday 7:00.

Thanks in Advance for all the help...!!!

NG

**Colin Legg** · 08-23-2011, 06:34 PM

Welcome to the forum, NG.

I've attached an example which contains your sample dates and times in column A.

To establish the revised start date and time, I used this formula in column B:

Please Login or Register  to view this content.

Where G2 contains the daily start time (07:00) and G3 contains the daily end time (19:00).

To calculate the end date and time I used this formula:

Please Login or Register  to view this content.

Where G7 and G8 contain simple formulas which work out how many actual days and hours should be added, accounting for the fact that your working days are only 12 hours.

Should you wish to change the working hours or the hours to roll forward then you can do so, but the formulae I have used do not allow for overnight shifts.

**Colin Legg** · 08-23-2011, 07:01 PM

There's a typo in one of the formulas on the original attached spreadsheet.

The formula in G8 should be:

Please Login or Register  to view this content.

I have attached an amended version.

Adding Business hours

LinkBack

Thread Tools

Rate This Thread

Display

Adding Business hours

Re: Adding Business hours

Re: Adding Business hours

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions