Forum Discussion

Copper Contributor

Aug 06, 2022

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...

excel

Formulas and Functions

HansVogelaar
Aug 06, 2022
RobertoKartoredjo

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")

RobertoKartoredjo

Copper Contributor

Aug 12, 2022

HansVogelaar

Hi Hans,

Thank you. This works! Thank you again for this.

Dankjewel

RobertoKartoredjo

Copper Contributor

Aug 13, 2022

@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?

Test file.xlsx10 KB

HansVogelaar
MVP
Aug 13, 2022
RobertoKartoredjo

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.