Hi everyone. I am not even sure if this is possible to do...
Basically I invoice my company weekly and checks get cut on the 10th and 25th of every month. BUT if the check cut day falls on a Saturday it gets done on Friday and if it falls on a Sunday it gets cut on Monday. Is there a formula that will calculate the actual "pay date" for me?
Here is an example
Last edited by shanshine; 09-28-2011 at 12:52 PM.
Try:
=B2+IF(WEEKDAY(B2,2)=6,-1,IF(WEEKDAY(B2,2)=7,1,0))
copied down
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
Thank you!!!Originally Posted by NBVC
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?
See attached... with a few hidden columns with intermediate formulas...
Last edited by NBVC; 09-21-2011 at 05:06 PM. Reason: Updated workbook
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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
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?
Audere est facere
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
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
Audere est facere
It gave me a weird numbers....
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]
Audere est facere
Awesome...I feel dumb! Thanks!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks