• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Working Day 4th June 12; Australia WA vs. UK.

C

cafepassione

New Member
Hi all Thanks in advance.


Appreciate quick replies (as this is driving me crazy). quick response as this is driving me crazy. This is about working day in Excel 2010. I am creating a timetable (update monthly) with a long list of tasks.


background

My location is in (perth, western australia based) but company headquarter is in London, UK. 4th June 12 (WD2) is public holiday for Perth only, not for London (means I still need to finish reports on WD2). Currently, when I use those formulas to calculate working days, excel just automatically remove 4th June as WD2, instead currently showing 5th June.


My question

1. It seems to be Excel default my calender to Perth, so it doesnt count 4th June 12 as a working day, so how can I make Excel see 4th June is a working day?


Criteria:

1. Working day numbering convention can't be changed (company convention).

2. preferably, not to change the default calender for the region (as sometimes, the situation can be vice versa i.e. they work, i dont work!)

3. No Marco is allowed.


how can I solve this problem?

or any formula you can suggested?


Excel version: 2010; regional setting: Perth, Western Australia.
 
I would think that you need to get some informaton about the user or their country location, and you are not going to get that without VBA.


BTW, I live in the UK, and as far as I am aware, 4th June is a bank holiday, as is the 5th.
 
@xld,


4th June is a Bank Holiday, but 5th June is a Public Holiday as it will only happen this year for the Queen's Diamond Jubilee
 
Cafepassione


Firstly, Welcome to the Chandoo.org forums


Excel does not use the Computers calendars for calculation of formulas for Working Days etc

You need to define the calendar yourself and so whether it is London or WA the choice is yours.


EG: If D4 has 28 May =WORKDAY.INTL(D4,5) = 4 June

even though June 4th is a Holiday in Perth


To allow for the Australian Holidays list them in say N1:N10

Then use =WORKDAY.INTL(D4,5,,N1:N10)


ps: Happy to catchup for a Coffee at any time in Perth or surrounds
 
You must log in or register to reply here.
Back
Top