Hello,
Can you to modify the code.
I want 20 days return date from the date I entered in E3.
Please Login or Register to view this content.
Let say if I enter 3/25/2024 then the date return should be 04/19/2024. Included all the weekends and the holidays that I have put in the W28:W999.
From the code I want to removed W28:W999 that where I placed the holidays.
Thanks
If you want 20 days instead of 15 then change the 15 to 20. If you want to ignore holidays from the calculation, then omit the range at the end. Like this:
=IF(E3="","",WORKDAY(E3,20))
Hope this helps.
Pete
Hi Pete
Sorry… I just thought of that i made a wrong scenario.
I actually need to include the W28:W999. This is a holiday we have in the office.
I need the result to falls on the weekdays not on the weekend nor the holidays that’s on the W column because I can’t set schedule for the weekdays. Id my results falls on Sunday then my result should be the Monday instead
thanks
I don’t want to type all the weekends with the holidays in W columns.
WORKDAY formula
=IF(E3="","",WORKDAY(E3,15,W28:W999))
never fall into weekend/holiday.
In case you want E3 not fall in to weekend/Holiday, -1 and +1 may help:
=IF(E3="","",WORKDAY(E3-1,15+1,W28:W999))
Quang PT
If you do not want to type your holidays in column W, then =IF(E3="","",WORKDAY(E3,20,{"date1","date2"...})), replacing date1 with the 1st holiday date, date2 2nd etc.
Hello all,
I have attached the workshseet here. I using 5 days return to make sure as a sample.
I just find out that if we use the return date from today, if its falls on the weekend or holiday then, we need to use the weekend before not the forward weekdays.
Let say today is 03/26/24 so if we count the 5 then the result is 03/31/24 (Sunday). My return should be 03/29/24 to fall back to weekdays (Not 04/02/24).
Another sample, if I enter the date for 04/02/24 then the result should be 04/05/24 (not 04/08/024)
Thank you in advance!
Please Login or Register to view this content.
Suppose cell A1 contains a date.
The date after adding 5 days is: A1+5.
If you want A1+5, and if it falls on a weekend or a holiday, and you want to move it to the nearest weekday, there's a trick:
Increase A1+5 by 1 day (A1+6), then move it back 1 day using the WORKDAY function.
If A1+5 is a weekday, nothing will change. But if it falls on a weekend or a holiday, it will be moved back by 1 working day.
The result will be:
Please Login or Register to view this content.
If return date falls on a weekend or a holiday and you want to deem the next available working day as the return date, e.g. return date on 6th January (Sat), the next working day is 8th January (not a holiday), then:
5 days from return date (not counting return date as 1 day):
=WORKDAY(WORKDAY(E3-1,1,W28:W907),5,W28:W907)
5 days from return date (counting return date as 1 day):
=WORKDAY(WORKDAY(WORKDAY(E3-1,1,W28:W907),5,W28:W907),-1,W28:W907)
Hello everyone,
Ok... I came out with something that does work for me. Everything was perfect as I ran or entering the date in E3 but when I enter 02/20/24 and it appears 02/25/24 which is weekend. It should select the 02/26/24. Is this because there is a 29 days in February?
Here is the new created worksheet. This sample has 5 days
Please Login or Register to view this content.
"0000000" parameter mean "no weekend"
If weekend is Sat and Sun, it should be:
"0000011"
Hello everyone,
I really need to finish this project. But it seems I am having trouble getting what i am trying to accomplish.
In my worksheet I added a picture where it supposed to have the result.
5 Calendar day from 2/20/24 iis 2/26/2024
5 calendar day from 2/21/24 is 2/26/2024
5 caelndar day from 2/22/24 is 2/27/2024
5 calendar day from 2/23/24 is 2/28/2024
but in the worksheet it not getting what it supposed to be.
Please fix the formula or maybe change the formula to make it happen.
Thank you
What is the logic that 5 calendar days from 2/20/24 (Tuesday) and 2/21/24 (Wednesday) are both 2/26/2024 (Monday)?
Do your weekends not fall on Saturdays and Sundays?
Hi
If u count from 2/20 then 2/21, 2/22, 2/23, 2/24, 2/25 then its 2/25 but because 2/25 is sunday then i want to fall on 2/26
now
count from 2/21 then 2/22, 2/23, 2/24, 2/25, 2/26 so it is 2/26 because is a good weekday.
I want 5 dates to fall on weekdays not weekends or the dates thatbis located in sheet2.
This is my last attempt at answering you:
Return Date.png
As mention in mypost #5, (End Date) - 1 then +1 may help the result fall out of WK/HOL
Please Login or Register to view this content.
Sorry to say, Quang, your formula is not foolproof - please see attached workbook.
DATERETURN JT.png
It's a FORMULA, not code, so I've moved the thread out of the VBA section and into the Formulas & Functions section.Can you to modify the code.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Originally Posted by RJ1969
Hi JT, I notice that the OP doesn't mention wanting to work 5 consecutive days, starting from a specific day; rather, they want to work 5 calendar days in total. However, if the end date falls on a weekend or holiday, it will be adjusted to the next weekday.
That is what my formula did
Hello,
Haven't resolved this issue yet.
I need a formula that if I enter date in E3 the result should show (see below).
5 calendar days from 02/20/24 is 02/26/24
5 Calendar days from 02/21/24 is 02/26/24
5 Calendar days from 02/22/24 is 02/27/24
5 Calendar days from 02/23/24 is 02/28/24
Sheet2 is the dates our office are closed.
At the attachment worksheet if I enter 02/20 result is 02/26/24 which is correct.
But if I enter 02/21/24 is 02/27/24 is incorrect and if I enter 02/22 result is 02/28/24 is also incorrect.
I need the formula where all the dates should be resulted as I wished for.
Thank you
Not sure I understand your logic. Why can't you just use:Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I think your expected answer for the 20th is incorrect. Please explain WHY you think it should be the 26th.
Try:
=WORKDAY.INTL(E3,5,"0000000",Sheet2!$A$1:$A$42)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
@Glenn: RETURN DATE 5 DAYS CALENDAR DATE INCLUDING WEEKENDS is specified in the sample file.
Trevor, I don't follow you. In any event, I'm away out now for dinner and a beer.
Not sure I do. A bit confusing. Enjoy!
Hi
Still not working as it supposed to have a result.
Ok... let me try.
If I enter 02/20/24 in E3, if you count start from 21,22,23,24,25 the 5 count day 02/25/24 but since 02/25/24 is not a good because its Sunday (our office closed on weekend) then the result should show 02/26/24.
Now, If I enter 02/21/24, if you count start from 22,23,24,25,26 the 5th day count is 02/26/24 it's a good day because our office is open.
Lastly, if I enter 02/14/24, if you start counting from 15,16,17,18,19 the 5 days calendar is the 19th but since the 19th is holiday (I have the office holiday in Sheet2) then it should follow the next day the 02/20/24.
I hope I explain very clearly.
5 calendar days from 02/20/24 is 02/26/24
5 Calendar days from 02/21/24 is 02/26/24
5 Calendar days from 02/22/24 is 02/27/24
5 Calendar days from 02/144/24 is 02/20/24
How can you count 2/25/24 in the first calculation but not the second? It's still not a working day.
I think Glenn's formula should be:Formula:Please Login or Register to view this content.
Hi TMS
So I tried this formula and replaced Glenn's formula but I still not getting the result that I wish for.Please Login or Register to view this content.
In my worksheet I sent there is a calendar.
Try to enter 02/21/24 and start counting from Feb. 22,23,24,25,26 the 5th day is 02/26/24 which is a good day. Our office is closed on weekends and holidays(Sheet2).
I hope I explained clearly.
No, not really. If your office is closed on the Sunday ( 02/25/24 ), you need to exclude it from all the calculations, not just the first one. It's not a working day, regardless of whether or not the count lands on it.I hope I explained clearly.
Hi
The weekends and Holidays(Sheet2), yes it should be excluded but its included in counting the days.
How can it be excluded but included in counting the days? It doesn’t make any sense to me, so I’m going to take a step back and let someone else have a go.
What I meant is that when we count days from the day we start, we do include counting the weekends and holidays. But if it falls the 5th day on weekends or holidays then we have to use the following weekday.
Thanks
Ok, I think I understand. Not sure how to get what you want.
Maybe this:Formula:Please Login or Register to view this content.
Goodnight
TMS
ok... I tried it and it worked but I Tried to enter 2/13/24 and it appears 02/19/24 (Holiday - Sheet2) supposedly 02/20/22. Tried to enter 04/15/24 and it appears 04/20/24 (Saturday) it supposed to appear 04/22/24. But some of the days appears ok. You can try to enter in the sample worksheet I sent previously and you will see what it does.
thanks
I remember I addressed this issue in your previous topic, post number 16. Pls come back to read my clarification carefully.
https://www.excelforum.com/excel-for...ml#post5935841
However, I haven't heard back from you since then. When I retested that configuration, the results came back as you desired.
Please Login or Register to view this content.
Hi Bebo
The formula is not giving the result as i want it.
TMS formula is actually guving me the way I want it except the 2/14 and and 4/15 entry that still giving me the saturday and holiday result as I noticed when I did an entry on those dates.
thank you
Based on this logic, Bebo's formula should work.
Last edited by josephteh; 04-13-2024 at 11:16 AM.
If it doesn't work on certain dates, tell us what is the expected result.
Last edited by josephteh; 04-13-2024 at 12:18 PM.
Hi Bebo,
I apologize. Your formula works perfect! I am not sure what happened because when I tried it did not work before. But I gave it a try and it worked perfect. Thank you so much.
Can I request just very last thing. This will work for the other worksheet. I have two worksheet I am working on. One worksheet has been resolved with your formula.
My other worksheet it's pretty much the same but if falls Monday-Thursday, I want the result to be the following Friday.
example: 04/15/24 result is 04/22/24 (on your code) then the following Friday is 04/26/24. In otherwords, anything tha will falls on M-Thursday then it should be Friday. But if falls Friday (Holiday) then the good following Friday. I think there is a Friday that is holiday in sheet2
I am not sure if this will be possible.
Please Login or Register to view this content.
Last edited by RJ1969; 04-13-2024 at 09:27 PM.
Since Bebo resolved my first issue about the formula, Should I place this second issue to a different post?
Try this:
With only Friday (0) is working dayPlease Login or Register to view this content.
Hi Bebo
It showed #NAME? after I put this formula =WORKDAY.INTL(E3+5-1,1,"1111011",Sheet2!$A:$A)
It still works for me.
Try to refresh the spreadsheet (Hit F9) then it should works.
According to the MS web page for WORKDAY.INTL, it is not available in Excel 2010.
See: https://support.microsoft.com/en-us/...9-39611a9bf81d
TMS, thank you for your comment, it's true that WORKDAY.INTL isn't available in Excel 2010.
@RJ1969
in that case, using a UDF is also a good choice.
PHP Code:Option Explicit
Function Fri(cell As Double, num As Integer) As Date
Dim i&, n&, m&, c&, dat As Date, dat2 As Date
dat = cell + num
m = Array(5, 4, 3, 2, 1, 0, 6)(Weekday(dat) - 1)
dat = dat + m
c = WorksheetFunction.CountIf(Worksheets("Sheet2").Columns(1), dat)
If c = 0 Then
Fri = dat
Else
Do
i = i + 1
Fri = dat + i * 7
If Weekday(Fri) = 6 Then Exit Function
Loop Until i = 100
End If
End Function
Wow! Perfect. Thank you Bebo and TMS and to the rest.
I appreciated.
You're welcome.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks