Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default 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
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
Last working day of month JudithJubilee New Users to Excel 5 June 6th 07 02:25 AM
=month formula no working PG Excel Discussion (Misc queries) 4 January 23rd 07 09:01 PM
amount of working days per month Nigel Excel Discussion (Misc queries) 2 November 29th 05 11:41 AM
Last Working Day In Month Winston New Users to Excel 9 November 3rd 05 05:49 PM
How to calculate the day before last two working day of each month Angus Excel Discussion (Misc queries) 1 June 29th 05 12:22 PM


All times are GMT +1. The time now is 11:51 AM.

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

About Us

"It's about Microsoft Excel"