A few date queries for a payroll calendar

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Jun 10, 2010

• #2

mcinnes01 said:

My last working day in the month calculation works fine, except for july which brings a #Value! error.

Click to expand...

The last working day can be calculated using:

Code:

=WORKDAY('HOLIDAY FORMULA'!J10+1,-1,'HOLIDAY FORMULA'!$B$3:$B$11)

The key is to add one day to the last day of the month in question and then set working day requirement as -1
In essence: find last working day the occurred prior to 1st of the following month.

mcinnes01 said:

How do I find bank holiday dates for christmas where christmas day is say saturday and boxing day is sunday, therefore i would need the bank holidays as monday and tuesday.

Click to expand...

You should list the public holidays as 27th & 28th respectively in your public holiday range - if you Google you will find public holiday listings.

mcinnes01 said:

Finally how do I calculate 2 working days before a date, excluding bank holidays.

Click to expand...

You might get away with:

=WORKDAY(date,-2,holiday range)

 

Upvote 0

M

mcinnes01

New Member

Joined

Jun 7, 2010

Messages

38

• Jun 10, 2010

• #3

Hi,

thanks for your advise it has been a great help and the majority of date calculation for monthly cutoff's is done now. I was a little unclear though with my question regarding christmas day and boxing day. i want my calendar to be dynamic therefore i don't want to manually enter any date other than the year in which I want it to produce a calendar for.

How can I calculate what date the bank holiday for, new year, may, spring, summer and christmas fall on. E.g. working day. so if christmas was on saturday the expression would see that and return a date of monday the 27th and then boxingday would be sun 26th so would return a date of tuesday 28th.

How can I calulate august bank holiday e.g. last monday in august?#


Thanks for your help,

Andy

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Jun 10, 2010

• #4

Dynamically establishing public holidays is non trivial.
That fact coupled with the minimal effort it takes to update a listing once a year makes the process not really worth the effort - but that's my opinion.

Locale unknown but for say a UK listing see:
http://www.direct.gov.uk/en/governmentcitizensandrights/livingintheuk/dg_073741

Calculating the likes of Christmas etc is straightforward enough (adjust based on weekday), however, the likes of Easter etc are not so straightforward, see: http://www.cpearson.com/excel/Easter.aspx

 

Upvote 0

M

mcinnes01

New Member

Joined

Jun 7, 2010

Messages

38

• Jun 10, 2010

• #5

I have a calulation for easter that is quite rediculous but is 100% accurate taking into account the moon and the 5.7m cycle of easter. don't ask but it works! my only problem is the more simple bank holidays as described.

Can you give me an example for new years day, would it be something along the logic of:

if('HOLIDAY FORMULA'!B3=WORKDAY('HOLIDAY FORMULA'!B3,-1,0,0,0,0,0,-1),'HOLIDAY FORMULA'!B3,WORKDAY('HOLIDAY FORMULA'!B3,0))

looks wrong as im not sure how to specifiy if the date falls on a weekday for the if statement.

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Jun 10, 2010

• #6

It's not very clear what date resides in 'HOLIDAY FORMULA'!B3 ... you mention you wish only to specify the year... so what are you storing for Christmas Day ?

edit: I don't know why I had 25th / 26th in my head... that's now what you asked !

You would need to hardwire the Boxing Day calc such that if the 26th should fall on a weekend day (Sat,Sun) it must occur on the 28th else if Monday then 27th else 26th.

Christmas Day itself has a few more variations ie if 25th fell on a Sat you must add 2 days, for Sun you would add 1.

Re: New Year Day - you've asked this on another forum and have an active discussion - we prefer to avoid x-posting on multiple forums so I will not answer that question here.

 

Last edited: Jun 10, 2010

Upvote 0

M

mcinnes01

New Member

Joined

Jun 7, 2010

Messages

38

• Jun 10, 2010

• #7

The 'HOLIDAY FORMULA'!B4 is taking the year from the date on the frontend of the calendar. So it contains the year to make the calendar variable.


I thought by using chrsitmas day for example in the date format:

date('HOLIDAY FORMULA'!B4,12,25) within the calculation would be the best way of storing the date of christmas, that way the date stored in the spreadsheet would be the resultant bank holiday date.

This in mind how can would I go about getting excel to tell me the next WORKDAY if the 25th is a saturday or sunday.

I am quite noob with the workday and most date expression actually and so I am struggling a little. How do you express weekends i saw something somewhere which i cant find now that had some form of date then
-1,0,0,0,0,0,-1

which i guess signifies saturday and sunday? im not sure if you understand this could you please clarify it for me.

Thanks for your help! i need it!

 

Upvote 0

M

mcinnes01

New Member

Joined

Jun 7, 2010

Messages

38

• Jun 10, 2010

• #8

=IF(WEEKDAY(DATE($I$4,12,25))=7,DATE($I$4,12,25+2),IF(WEEKDAY(DATE($I$4,12,25))=1,DATE($I$4,12,25+1),DATE($I$4,12,25)))

this seems to work can I have some feedback please?

 

Upvote 0

M

mcinnes01

New Member

Joined

Jun 7, 2010

Messages

38

• Jun 10, 2010

• #9

So heres what i've got so far:

New Year:

=IF(WEEKDAY(DATE($I$4,1,1))=7,DATE($I$4,1,1+2),IF(WEEKDAY(DATE($I$4,1,1))=1,DATE($I$4,1,1+1),DATE($I$4,1,1)))

Easter:

From previous forum entry

Xmas:

=IF(WEEKDAY(DATE($I$4,12,25))=7,DATE($I$4,12,25+2),IF(WEEKDAY(DATE($I$4,12,25))=1,DATE($I$4,12,25+1),DATE($I$4,12,25)))

Boxing Day:

=IF(WEEKDAY(DATE($I$4,12,26))=7,DATE($I$4,12,26+2),IF(WEEKDAY(DATE($I$4,12,26))=1,DATE($I$4,12,26+2),DATE($I$4,12,26)))


Can anyone see any problem in these calculations they seem to be right for this year, but as i said my the date functions are completely new to me so this is a learning curve.


Finally I can't work out how to get the early may, spring and summer bank holidays.

These are respectively: first monday of may, last monday of may and last monday of august.


Thanks for everyones help so far!!

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Jun 10, 2010

• #10

Note: that you now have the contribution of daddylonglegs over at ExcelForum - widely regarded as one of the worlds best when it comes to working with dates in Excel - I'd recommend you follow up there.

http://www.excelforum.com/excel-2007-help/732569-day-a-holiday-falls.html

On the same link I also provided a formula along the lines of your post but re: Boxing Day (you can adapt for Christmas Day also)

Edit: posts now crossing.

 

Upvote 0