I am starting with a completion date and using formulas to calculate deadline dates based off of the completion date. My issue is that if a calculated date lands on a Saturday or Sunday, how to I have it revert to the Friday before the Saturday or Sunday?
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I have attached a copy of the workbook
thank you!
in J8
=IF(WEEKDAY(G8)=1,G8-2,IF(WEEKDAY(G8)=7,WEEKDAY(G8)-1,G8))
and copy down
Last edited by alansidman; 01-06-2019 at 09:06 PM.
Try wrapping your existing formulas in column G and in range B36:B43 in a WORKDAY(your_formula +1,-1) function.
For example, your formula in cell G32 (currently, =C33-3) would become =WORKDAY(C33-3+1,-1)
Another option...
=IF(G8="","",IF(WEEKDAY(G8,2)>5,G8-WEEKDAY(G8,2)+5,G8))
format as long date, then copy down
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
thank you for all the assistance, now I have more question. I need to take a date and then add 20 days to it, if that day is a Monday, then the date stands, if the new calculated date is any other date, I need it to go to the following Monday. For reference on the attached spreadsheet is is C7.
Is this what you need in cell G7?
=WORKDAY.INTL(G6+20-1,1,"0111111")
the above calculation worked in all but two cells. For some reason two of the cells go to a date in 1900 and I am not sure why. I copied the formula from the direct cell above, i am not sure what the issue is?
Pasting the formula into row 6 and dragging down it yields a result of Monday Jan 23, 1900 if the corresponding cell in column G is blank. Other than that the dates are in 2019 and seem to be correct. Please let us know which cells are displaying incorrect dates or, better yet, upload another sample showing where the issue is occurring.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks