Aug 06 2022 05:10 AM
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 |
Aug 06 2022 06:49 AM
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
Aug 06 2022 07:10 AM
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 |
Aug 06 2022 08:27 AM
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.
Aug 06 2022 09:28 AM
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 |
Aug 06 2022 12:52 PM
SolutionPerhaps
=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")
Aug 12 2022 08:33 AM
Aug 13 2022 09:10 AM
@Hans Vogelaar@RobertoKartoredjo@Sergei Baklan@RobertoKartoredjo
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?
Aug 13 2022 09:21 AM
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.
Aug 06 2022 12:52 PM
SolutionPerhaps
=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")