Reversal calculated workday without holidays - EXCEL

0
votes

I need a little bit help form Excel masters :)

I have a table with days of the month and I want to calculate remaining workdays like this:

  1. DATA , DAY, DATA, REMAINING WORKDAYS
  2. 01.04.2018, Sunday, "data" , 0
  3. 02.04.2018, Monday, "data" , 19
  4. 03.04.2018, Tuesday, "data" , 18
  5. 04.04.2018, Wednesday, "data" , 17
  6. 05.04.2018, Thursday, "data" , 16
  7. 06.04.2018, Friday, "data" , 15
  8. 07.04.2018, Saturday, "data" , 0
  9. 08.04.2018, Sunday, "data" , 0
  10. 09.04.2018, Monday, "data" , 14

So ... the formula in "Remaining workdays" have be calculated automatically and when the day is Saturday and Sunday or Holiday have to jump to next workday

10x in advance

1
excel
How is remaining workdays 19 at 02/4/18? What is your working week? Are you excluding holidays? If you are including, what are the dates? UK for example would have 20 days incl holiday and 21 excl. - QHarr
My bad, working days for April are 21, but in my country 6.04.2018 and 9.04.2018 are not working days...so for that in this case working days are 19 - Dejan Ivanov
Why do your weekend days show 0 remaining workdays instead of 19 or 14 in your above table? - Ron Rosenfeld
No need it to calculate in column Data - Dejan Ivanov

1 Answers

1
votes

Then

=IF(OR(WEEKDAY(A2,2)=6, WEEKDAY(A2,2)=7), 0,NETWORKDAYS(A2,EOMONTH(A2,0),$G$1:$G$2))

Where G1:G2 have your holiday dates. Drag formula down rows as required. See image.

A2 has your first date as a recognised date format.

Data

You say that 09 is a holiday so this;

09/04/2018 Monday "data" 14

Should still be at 15.