Bank Holiday formula in Excel 1

[seabubble]

Hi All, I have the following formula in a spread sheet. It works out the total SLA hours in the week from start date to end date.
=IF(F14>0,10/24*(NETWORKDAYS(E14,F14)-2)+TIME(18,0,0)-(E14-INT(E14))+(F14-INT(F14))-TIME(8,0,0),"Unresolved")

This works well if the times are weekdays between 8 and 6pm, and also deducts weekends.

Can anyone tell me how to subtract bank holidays if they are in the week? I have been dragged across the hot coals for missing this! ouch.

 

[mrmovie]

there are more bankholidays in Munich than in Bracknell.
I would suggest you would need to maintain a custom list of bank holiday days you are interested in. for instance we have funny unscheduled ones this year due to crimbo etc

 

[seabubble]

Thanks for replying MrMovie. I was wondering if the dates are held in part of the sheet or a text document. If they are how would I use them?

 

[mrmovie]

the only place i know of this information being held/supplied by someone is a file used to Outlook, it is used to display 'special days' like St Andrews day etc.
i doubt this is what you are after though.
i guess you would need a function which you could pass mondays date. the function would then loop through mon-fridays days date and check against a dictionary object? or array of valid bank holidays.
the function could return numberofDays in the week? ie. 4 if it finds a bank holiday??

 

[KenWright]

Holidays are an optional argument in the NETWORKDAYS function, eg

NETWORKDAYS(start_date,end_date,holidays)

And straight out of help:-

Important Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Start_date is a date that represents the start date.

End_date is a date that represents the end date.

Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[seabubble]

I have tried to do the following
=IF(F14>0,10/24*(NETWORKDAYS(E14,F14)-2)+TIME(18,0,0)-(E14-INT(E14))+(F14-INT(F14)-(3/1/2005))-TIME(8,0,0),"Unresolved")

What happens is that it takes the 8 hours off even when the holiday date is not withing the start and end times!

 

[mrmovie]

dont you need the HolidayDays parameter in NETWORKDAYS(E14,F14,somethinghere) or am i way off base again ;-)

 

[seabubble]

Hey its working! Thanks. I will now try and get the formula to look at a range so I can add the dates I need.

 

[xlbo]

seabubble - for future reference, pleas epost questions relating to FORMULAE in the MSOffice forum. This forum is for VBA code

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[seabubble]

I can now get the formula to look at the date range and work for one cell. BUT if I drag the formula to the next cell the holiday data cell changes by one.
ie .(E14,F14,GraphData!A2:A3) changes to(E14,F14,GraphData!A3:A4)

I know there is something to fix the range but cant remember. (Getting old)

 

[mrmovie]

$$$$$

 

[seabubble]

sorry xlbo I thought I was in the right one. Will not post here again.

 

[xlbo]

If you have a VBA question, please feel free to do so. No need to apologise - as I said - for future ref. only

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question