IF & And statement help

[IronMan1982]

I'm trying to put together a formula that will shift the contents of a cell to the left if the day is selected as non business day...basically if something falls on a bank holiday i need the spreadsheet to move the contents to the left to the last normal business day before the bank holiday.

I came up with this...but I don't think it's the best solution (in the highlighted green cell) it works fine for the Friday. But if it fell on Monday I need it to fall back to thursday 30th april. I hope all that makes sense.

VBA isn't my strong suit but I was thinking if it would work better with a colour based IF statement perhaps? so for example if the cell colour is grey move all the contents to the left to the first normal business day before the bank/public holiday.

=IF(N$3="Non-Business",OFFSET(M$3,$E18,1),IF(IFERROR(VLOOKUP(CONCATENATE($H18," ",$I18," ",M$4),'Input Sheet'!$B$3:$E$19,4,0),0)=M$4,1,IF(IFERROR(VLOOKUP(CONCATENATE($H18," ",$I18," ",M$5),'Input Sheet'!$B$3:$E$19,4,0),0)=M$5,1,0)))

thanks in advance for any help.

 

[Rijnsent]

Hi IronMan,
I miss a bit here: for your green cell you're trying to lookup the value of "3030 USD Thursday" in 'Input Sheet'!$B$3:$E$19, I assume thursday has the date in it formatted showing only the day? But than you want to add a 1 if there is also a value for "3030 USD Friday" if that Friday happens to be a Non-Business day? And add a value for the Saturday too?
Thanks for some clarification,
Koen

 

[IronMan1982]

Hiya,
Yes that's right it is formatted just to show the day NOT the date.
Yes right again in regards to add a "1" if there is a value for the Friday when it's been selected as a "non business" day
Yes, at times there will be payments for a FIXED DATE no matter when that date falls, so if it fell on a weekend I need to show that it needs to go out on the friday before the weekend due date.

I hope that clears thing up.

Thank you for taking the time to reply.

 

[Rijnsent]

Hi IronMan,
I'd solve your issue with some COUNTIFS. In this way:

• Add this formula to M7: =IF(M3="Business",HLOOKUP("Business",M3:S4,2,FALSE),M6-1) -> that basically gives you the range of dates that need to be checked (assuming not more than 6 Non-Business days in a row, otherwise do e.g. Z4 instead of S4). If the day is non-business the "end date" is before the "start date", so won't give results.
• Next, in M18: =COUNTIFS('Input Sheet'!$C$3:$C$19,$H18,'Input Sheet'!$D$3:$D$19,$I18,'Input Sheet'!$E$3:$E$19,">="&M$6,'Input Sheet'!$E$3:$E$19,"<="&M$7) -> this assumes you have on your Input Sheet a list of dates in column E, the numbered code (3010 in your screenshot) in column B and the currency in column C. That COUNTIFS gives back the total occurences, based on the date range that you created in row 6&7.
• If you have an amount in Input Sheet, you could easily use SUMIFS to add them

Hope that works,
Koen