Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First working day of the month
I need a formula to calculate the first working day of the month. At present
I am using: =DATE(YEAR(B9),MONTH(B9),1+IF(WEEKDAY(DATE(YEAR(B9 ),MONTH(B9),1))=7,2,IF(WEEKDAY(DATE(YEAR(B9),MONTH (B9),1))=1,1,0))) where B9 is today's date. However, this does not take into account bank holidays (such as 1 January). This spreadsheet is for use by other users and needs to be as automated as possible so I don't want to override the date cell if I can help it. Does anyone have a simple formula to calculate the first working day of each month, taking into account bank holidays? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First working day of the month
Use
=WORKDAY(B9-DAY(B9),1,holidays) where holidays is a list of holiday dates. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Hedgie" wrote in message ... I need a formula to calculate the first working day of the month. At present I am using: =DATE(YEAR(B9),MONTH(B9),1+IF(WEEKDAY(DATE(YEAR(B9 ),MONTH(B9),1))=7,2,IF(WEEKDAY(DATE(YEAR(B9),MONTH (B9),1))=1,1,0))) where B9 is today's date. However, this does not take into account bank holidays (such as 1 January). This spreadsheet is for use by other users and needs to be as automated as possible so I don't want to override the date cell if I can help it. Does anyone have a simple formula to calculate the first working day of each month, taking into account bank holidays? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First working day of the month
If you (and everyone who will use the workbook) has
the Analysis ToolPak add-in installed, you could use the WORKDAY function. However, if there is risk that some of the users will NOT have that add-in, try this: B9: (a date) G1:G10 contains a list of bank holidays.....eg G1: 2008-01-01, etc This formula (in sections, for readability) returns the 1st workday of the month: =MIN(IF((COUNTIF(G1:G10,B9-DAY(B9)+{1,2,3,4,5,6,7})=0)* (WEEKDAY(B9-DAY(B9)+{1,2,3,4,5,6,7},2)<6),(B9-DAY(B9)+{1,2,3,4,5,6,7}))) Is that something you can work with? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Hedgie" wrote in message ... I need a formula to calculate the first working day of the month. At present I am using: =DATE(YEAR(B9),MONTH(B9),1+IF(WEEKDAY(DATE(YEAR(B9 ),MONTH(B9),1))=7,2,IF(WEEKDAY(DATE(YEAR(B9),MONTH (B9),1))=1,1,0))) where B9 is today's date. However, this does not take into account bank holidays (such as 1 January). This spreadsheet is for use by other users and needs to be as automated as possible so I don't want to override the date cell if I can help it. Does anyone have a simple formula to calculate the first working day of each month, taking into account bank holidays? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Last working day of month | New Users to Excel | |||
=month formula no working | Excel Discussion (Misc queries) | |||
amount of working days per month | Excel Discussion (Misc queries) | |||
Last Working Day In Month | New Users to Excel | |||
How to calculate the day before last two working day of each month | Excel Discussion (Misc queries) |