# Date calculation problem when calculating vacation time used/remaining

#### markyns

##### New Member
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 1st and 2nd 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 1st 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,

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### pjmorris

##### Well-known Member

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

##### New Member
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

##### Well-known Member
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

##### New Member
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

#### pjmorris

##### Well-known Member

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,675
Messages
5,838,714
Members
430,566
Latest member
ChanchalSingh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back