Posts by IronMan82

Here is my example spreadsheet, hope that helps. So once again, in a nutshell if the day is selected as a non business day I need the contents of those cells to move back (left) to the last business day before the non business day...

IE... Saturday goes back to Friday, Sunday to Friday, Monday back to Friday, and if Friday was a non business day then it moves back to Thursday.

So Easter weekend for example in the UK would mean Good Friday, Sat, Sun and Easter Monday are non business ... so anything on these days would all be pushed back to the Thursday before Good Friday. I just cannot figure out how to do it.

Thanks Payment Cal example - formula working.xlsx

I cannot for the life of me figure this out...in a nutshell if the column is changed to Non-Business it gets highlighted grey and it's contents need to be moved back to the last business day before it...so if it's a Friday it needs to move back 1 day to Thursday, a Monday would need to go back 3 days to the Friday,

if you look at the highlighted green cell below, i used this formula:

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

But i think it's over complicated and I cant replicate it over the the other columns. I wasn't sure if it would be better to use an IF statement and use some vba code to move it via colour instead? The main problem is I have no idea how to do that either. Any help will be really appreciated.

Thanks