Forum Discussion
Return latest date for billing
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")
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 |
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")
@Hans VogelaarRobertoKartoredjo@Sergei BaklanRobertoKartoredjo
Im sorry to bother you guys again but they put in yet another variable in the Excel file. Making it more challenging.
Looking at the first column. That indicates that the same VH is inspected for 2 different sites aka client. meaning they need to be billed for both. With the current formula it says that billing is "No" ( which is actually correct if there was just 1 site aka client).
Is it possible to add the "CLIENT" column also in the formula?Perhaps
=IF(NETWORKDAYS.INTL(MAXIFS(B$1:B1,A$1:A1,A1,H$1:H1,H2),B2,IF(OR(WEEKDAY(B2)=7,WEEKDAY(MAXIFS(B$1:B1,A$1:A1,A1,H$1:H1,H2))=7),11,1))>5,"YES","NO")
but your sample workbook has so few data that I cannot test it meaningfully.