Workday not behaving as it should (Excel 2007)

[Teapotlid]

I'm trying to show deadline dates which are the 3rd working day of the month. I'm using =Workday(start date, number of offset days, Holidays). The Holidays element is not causing the issue (I get the same issue if I have Holidays or not) so it's not that. The examples I'll show, therefore, do not include holidays.
I put the first day of the month in column A and the Workday formula in column B. I'm using a dd-mmm-yy formatted date.
This is what I put in & what I get:
A2: 1-Mar-15
B3: =Workday($A2, 3) which gives the result of 4-Mar-15. This is correct, it's 3 working days into the month.
A3: 1-May-15
B3: =Workday($A3, 3) which gives the result of 6-May-15. This incorrect when you look at a calendar, it's 4 working days into the month.

To test it:

• I've checked my formulae & I've not accidentally written it as =Workday($A3,4) or anything obvious like that.
• I've checked A2:B3 using =Type & everything is a number; no text has sneaked in to cause issues.
• I used =Networkdays to check if there was something built into Excel date formulae that I just want getting. I used it to look at the difference between column A and B (again, ignoring any Holidays). Row 2 shows 3 days but row 3 shows 4 days. That means that Networkdays seems to be working even though Workday isn't.
• I've opened a standard MS Excel template & tried it out there too (as I initially did this in my work Excel template)
• I've ensured that I have the Analysis ToolPak add-in activated in case there was something in that which was needed to get things working as they should. It made no difference.

I'm now totally stumped!
I'd greatly appreciate any ideas on what could be causing this or any more checks that I could do.
Thanks
Teapotlid

 

[John_w]

Subtracting 1 from the start date gives the correct result:

=WORKDAY($A2-1, 3)
=WORKDAY($A3-1, 3)

 

[RonB1111]

The WORKDAY() function does not include the starting date in its calculations, but in your situation you need to include the starting date. Try this formula instead:

=WORKDAY(A2,IF(WEEKDAY(A2,2)>5,3,2))

 

[Teapotlid]

Thanks John.
Sadly this just gives me the same problem but with 2 and 3 days for March & May respectively rather than giving me 3 days regardless of the month.
Ho hum!

 

[Teapotlid]

Ron, thanks for this.
I get the same problem as I did with John's solution. I get 2 days for March & 3 days for May.
The problem is to get one formula which gives me 3 days regardless of month.

I really appreciate you guys thinking about it though!

 

[barry houdini]

I would use John_w's solution, I think it correctly returns the 3rd working day of the month in any scenario (unless there are so many holidays that there aren't 3 working days in the month!)

If A2 contains the 1st March 2015 then the suggested formula returns 4th March 2015 as required, for May it returns 5th May, which is also the 3rd workday

 

[John_w]

Are you sure?

A2: 1-Mar-15
B2: =WORKDAY($A2-1, 3) gives 4-Mar-15, the 3rd working day of the month.
A3: 1-May-15
B3: =WORKDAY($A3-1, 3) gives 5-May-15, also the 3rd working day of the month.

Remember, the above formulas don't account for holidays.

 

[Teapotlid]

Interesting, if you guys think it should work, and I think it should work, but it doesn't work... it makes me wonder if there is something a bit weird going on in the bowls of my PC that gets it confused about dates. I've had issues before (but I can't 100% remember what they were as I found workarounds).

I originally thought it may be something with the work Excel template as I know the person who wrote that was in the UK, the PC is a US configured PC & I'm in Canada. I wondered if there was some confusion around date formats/when a week starts etc because of this.

I'll try again on another PC & see if I get the same issue.

Thanks for your help with this. I'll report back!

 

[Teapotlid]

An interesting conundrum: I tried both Ron & John's solutions on a different PC & they both worked (thanks again guys!). Tried them again on my work PC & they didn't.
The only obvious difference is that my home PC is set up to use the non-US date format whereas my work PC uses US date format: but I don't see why that should change anything as the first day of the week is set to Monday on both PCs.

I think I may try re-installing excel as this is just weird!!
Thanks for all your help