Date calculation problem when calculating vacation time used/remaining

markyns

New Member
Joined
Apr 17, 2013
Messages
3
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,

Vladimir
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,972
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 17, 2013
Messages
3
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 (y)
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,972
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Apr 17, 2013
Messages
3
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
2osri8

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

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

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,972
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top