Forum Discussion
Return latest date for billing
I need a formula to help me with the billing process. I want it to help with if billing is YES of NO.
Please see below excel example.
Billing is YES if any equipment went to for inspection or went for inspection after 5 workdays. Billing is NO if any equipment went for inspection within 5 workdays. (not counting Saturdays and Sundays)
But if the equipment went for inspection on a Saturday the formula will need to count the Saturdays also.
Someone already helped me with the following formulas:
- IF(NETWORKDAYS(MAXIFS(A$1:A1,G$1:G1,G2),A2)>5,"YES","NO"). Here it only counts the workdays.
- IF(NETWORKDAYS.INTL(MAXIFS(A$1:A1,G$1:G1,G2),A2,11)>5,"YES","NO"). Here it doesn't count the Sundays only.
But somehow i need to put these 2 together. Cause if the equipment went for inspection on a Saturday it does need to count the Saturday + the normal workdays. And if an equipment went on a normal workday (Mon-Fri) it doesn't count the Saturday and Sunday.
| CLIENT | INSPECTION DATE | INSPECTION VALID TILL | INSPECTION LOCATION | BUSINESS PARTNER NAME | EQUIPMENT MODEL | EQUIPMENT NUMBER | LICENSE NUMBER | STATUS | STICKER NUMBER | BILLABLE |
| Mike | 23-Jun-21 | 20-Jul-21 | Parbo | FORD | BUS | POO174 | 85A6dB | PASSED | YES | |
| Brock | 16-Jul-22 | Parbo | HCA | BUS | POO174 | 85A6dB | FAILED | YES | ||
| Mike | 22-Jul-22 | 15-Aug-21 | Wanica | HCA | BUS | POO174 | 85A6dB | PASSED | NO | |
| Mike | 14-Aug-21 | 11-Sep-21 | Wanica | BLUE | BUS | POO174 | 85A6dB | PASSED | YES | |
| Brock | 18-Nov-21 | 10-Feb-22 | Parbo | BAY | D-MAX | POO1233 | 01w9hp | PASSED | YES | |
| Brock | 18-Nov-21 | 10-Feb-22 | Wanica | HCA | AMAROK | POO1234 | 3e71gz | PASSED | YES |
Perhaps
=IF(NETWORKDAYS.INTL(MAXIFS(A$1:A1,G$1:G1,G2),A2,IF(OR(WEEKDAY(A2)=7,WEEKDAY(MAXIFS(A$1:A1,G$1:G1,G2))=7),11,1))>5,"YES","NO")
8 Replies
That could be
=IF( NETWORKDAYS.INTL( E5, F5, 1 + 10*(WEEKDAY( E5, 1 ) = 7) ) > 5, "Yes", "No" )It takes as days-off Sat and Sun for any days but if start on Sat, otherwise Sun only
Thank you. But i do get the following below. On the 20 Aug the billing is supposed to be YES.
INSPECTION DATE INSPECTION VALID TILL INSPECTION LOCATION BUSINESS PARTNER NAME EQUIPMENT MODEL EQUIPMENT NUMBER LICENSE NUMBER STATUS STICKER NUMBER BILLABLE 5-Aug-22 CALYPSONA ARTKO NV BUS - CAT 3 3498BB FAILED YES 12-Aug-22 CALYPSONA ARTKO NV BUS - CAT 3 3498BB PASSED YES 15-Aug-22 CALYPSONA ARTKO NV BUS - CAT 3 2222CD FAILED YES 20-Aug-22 CALYPSONA ARTKO NV BUS - CAT 3 2222CD PASSED NO How about
=IF(NETWORKDAYS.INTL(MAXIFS(A$1:A1,G$1:G1,G2),A2,IF(WEEKDAY(A2)=7,11,1))>5,"YES","NO")in row 2.
