Forum Discussion
Solved
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 wen...
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")
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.
Hi Hans,
Thank you but,
I tried it and i get Billing "NO" on the 12th of Aug but this needs to be "YES" actually cause it does need to count the Saturday on the 6th when the equipment went for inspection the first time.
INSPECTION DATE INSPECTION VALID TILL INSPECTION LOCATION BUSINESS PARTNER NAME EQUIPMENT MODEL EQUIPMENT NUMBER LICENSE NUMBER STATUS STICKER NUMBER BILLABLE 6-Aug-22 CALYPSONA ARTKO NV TRUCK - CAT 4 33 4444RD FAILED YES 12-Aug-22 4-Nov-22 CALYPSONA ARTKO NV TRUCK - CAT 4 33 4444RD PASSED NO
