Date calculation problem when calculating vacation time used/remaining

[markyns]

Hello everyone,

I have a problem figuring out How to calculate used vacation time between two dates if I have 1 working Saturday. I try with NETWORKDAYS.INTL and I have 1^st and 2^nd parameter, start and end date, holidays also, but weekend parameter makes me problems. If I say for weekend parameter, use option 1 (Saturday and Sunday) and vacation took 3 weeks, then I have 1 working Saturday (every 1^st Saturday of the month is the working one) in to calculate, so I can’t use NETWORKDAYS.INTL properly. Can someone help me with a solution for this problem?


Many thanks,

Vladimir

 

[pjmorris]

Hi Vladimir,

Assuming that your start date is in Cell A2, and finish date is in B2, then in cell C2 enter: =A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+1) this will calculate the date of the first Saturday in the month.

Then wherever you want to calculate the number of holiday days taken enter: =NETWORKDAYS.INTL(A2,B2,1)+IF(AND(A2<=C2,B2>=C2),1,0) this calculates the holidays as if all Saturday/Sundays were holidays, but adds 1 to the sum if the holiday spans the first Saturday of the month. Note if you holidays span more than 1 working saturday then the formula will fail, but no one gets that much time of do they?

Hope this helps.

 

[markyns]

Thank You very much for the replay, this is just what i need and about those holidays, well, lets say it can happen , but if it does, ill just do it manualy

Thanks again m8

 

[pjmorris]

a pleasure to help.
I should just add that I didn't create the formula to calculate the first Saturday, I found it online - Google is great!

Regards

 

[markyns]

I have 1 more question, that kinda doesn't belongs much in this thread, but it it also does
If i want to repeat this calculation, lets say, 1st part of holiday and 2nd part on some other date span, each for few days, lets say 5 days and 8 days. I get the situation that i need to use 2 formulas in same cell. I got good result in one case, but if i delete 2nd To: date i got error in calculation. Im sure theres a better way to fix this but i just can't see it. Can you look at attached pictures and try to give me some advice?
You can write to me on vladimirmarceta <dot> gmail <dot> com

Download Correct.png from Sendspace.com - send big files the easy way

Download corrections.png from Sendspace.com - send big files the easy way

Download Error.png from Sendspace.com - send big files the easy way

 

[pjmorris]

Hi Vladimir,

I'm unable to access those images over this system - can you paste them into the forum?

However, based on your question I would suggest the easy answer is to merely treat them as separate holidays and then add them. I would expect you to get an error if one of the dates is missing, but you might be able to mitigate this by careful use of IF or IFERROR.

Regards