Open Times

[PhilMyWallet]

I have a spreadsheet which has data over a monthly period by 15 minute intervals (for 24 hours).

I have created a lookup which determines the open / close times for the data. This works fine during normal open hours, however if there is a special day (i.e. bank Holiday) where the open times are changed the report can not distinguish between a normal working days & a special day therefor the open times are wrong on the report.

I thought of using some additional lookup fields however there maybe several days in the month where the open hours differ from normal.

Can anyone think of any solutions around this?


Regards



Phil

 

[xlbo]

use a lookup table which stores your 'special' dates

pseudo syntax would be

=if(isna(vlookup(TheDate,SpecialDatesRange,1,false)),'do what you normally do','get special hours because there is a match to the special dates list')

 

[PhilMyWallet]

I already have a lookup which stores the days of the week. Each lookup determines the area of business then looks up the open times dependant on which day of the week it is.

I need to drill further on this as a secondary lookup to determine the date of the special day, then do the lookup.

Hope this makes sense. I just don't know whether I am looking too deeply.

 

[xlbo]

that is correct - you must have a seperate looku ptable of 'special days' that you will need to check before doing any processing