KX13ZN
New Member
- Joined
- Sep 12, 2019
- Messages
- 34
- Office Version
- 365
- 2019
- 2010
- Platform
- Windows
- Mobile
- Web
I have a formula that checks 7 cells in a row to see if they have a 1 in them. It then counts how many days have passed from a set date using those 1's to correspond to specific days.
=IF(P6=0,0,IF(D6=1,NETWORKDAYS.INTL(P6,TODAY(),"1111110"),0)+IF(E6=1,NETWORKDAYS.INTL(P6,TODAY(),"0111111"),0)+IF(F6=1,NETWORKDAYS.INTL(P6,TODAY(),"1011111"),0)+IF(G6=1,NETWORKDAYS.INTL(P6,TODAY(),"1101111"),0)+IF(H6=1,NETWORKDAYS.INTL(P6,TODAY(),"1110111"),0)+IF(I6=1,NETWORKDAYS.INTL(P6,TODAY(),"1111011"),0)+IF(J6=1,NETWORKDAYS.INTL(P6,TODAY(),"1111101"),0))
And Cell P6 contains the date.
The formula is set up like this because its based off of shifts which are not just mon-fri or sat-sun, they vary depending on the employee.
I need to convert that into vba so that I can add a new row with another macro and copy this equation in, doing it so the row number changes to the current row.
Is there way to set it so the equation auto updates or does it have to be completely re-written and if so how?
=IF(P6=0,0,IF(D6=1,NETWORKDAYS.INTL(P6,TODAY(),"1111110"),0)+IF(E6=1,NETWORKDAYS.INTL(P6,TODAY(),"0111111"),0)+IF(F6=1,NETWORKDAYS.INTL(P6,TODAY(),"1011111"),0)+IF(G6=1,NETWORKDAYS.INTL(P6,TODAY(),"1101111"),0)+IF(H6=1,NETWORKDAYS.INTL(P6,TODAY(),"1110111"),0)+IF(I6=1,NETWORKDAYS.INTL(P6,TODAY(),"1111011"),0)+IF(J6=1,NETWORKDAYS.INTL(P6,TODAY(),"1111101"),0))
D6 | E6 | F6 | G6 | H6 | I6 | J6 |
Sun | Mon | Tue | Wed | Thu | Fri | Sat |
And Cell P6 contains the date.
The formula is set up like this because its based off of shifts which are not just mon-fri or sat-sun, they vary depending on the employee.
I need to convert that into vba so that I can add a new row with another macro and copy this equation in, doing it so the row number changes to the current row.
Is there way to set it so the equation auto updates or does it have to be completely re-written and if so how?