Formula to determine day of week checks will be cut

Originally Posted by NBVC

Try:

=B2+IF(WEEKDAY(B2,2)=6,-1,IF(WEEKDAY(B2,2)=7,1,0))

copied down

Thank you!!!

Originally Posted by alansidman

Try this formula in C2 and copy it down

=IF(OR(WEEKDAY(B2)=2,WEEKDAY(B2)=3,WEEKDAY(B2)=4,WEEKDAY(B2)=5,WEEKDAY(B2)=6),B2,IF(WEEKDAY(B2)=1,B2+1,B2-1))

Alan

They both worked!! Thanks!

Ok I misguided a little bit on this one...SORRY!!!

That formula was exaclty what I asked for...I just asked for the wrong thing!

I want it to calculate the day that it will be due (without me manually filling it in) either the 10th or the 25th (whichever is the closest to 30 days without going over 30 days) and compensate for the day of the week that it will be cut. Basically the date outcomes were correct I just dont want to have to fill in the second column anymore (because I have put the wrong date in there once or twice)

Does that make sense?

Originally Posted by NBVC

See attached... with a few hidden columns with intermediate formulas...

Its calulating the wrong due date...the due date is the 10th or the 25th whichever is closest to 30 days without going over so.....I made a third column to show the expected pay date. Maybe that will make more sense

Originally Posted by daddylonglegs

Some of your dates seem to break that rule. Date in A6 is 21/8/2011 - add 30 days to that and you get 20/9/2011 so wouldn't the due date be 10th September? Same applies to row 8.....

Perhaps you could clarify - are the dates shown really the ones you need.......or is the rule wrong?

Hahaha see I just proved my point when I try and figure it out myself I sometimes screw it up!

Row 6 should be 9-9-11

Row 8 should be 9-26-11

Originally Posted by daddylonglegs

OK to do everything with a single formula......assuming you have week ending date in A2 use this formula in B2 for check cut date

=LOOKUP(WEEKDAY(A2+31-DAY(A2+21)+(DAY(A2+21)>15)*15),{1,2,7;1,0,-1})+A2+31-DAY(A2+21)+(DAY(A2+21)>15)*15

It gave me a weird numbers....

Originally Posted by daddylonglegs

Excel counts dates from 1900 - each day is 1 more so today is 40812....tomorrow is 40813 etc. just format the cells as dates to show as dates

[select range > right-click > Format Cells > Number > Date > mm/dd/yyyy or as required]

Awesome...I feel dumb! Thanks!!!