Question description:
This user has given permission to use the problem statement for this
blog.
I need to create a formula where column D becomes the date in column C + 5 business days when I enter a date into column C
Solved by X. Q. in 14 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
02/10/2018 - 03:18
Hello, I understand that you need help with adding business days to whatever date you add in column C, right?
User
02/10/2018 - 03:18
yes
Excelchat Expert
02/10/2018 - 03:18
Alright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
Excelchat Expert
02/10/2018 - 03:19
I may have a few quick question to help me fully understand your requirement.
User
02/10/2018 - 03:19
ok thank you
User
02/10/2018 - 03:19
you can tell mehow to do it so i can do it later though too?
Excelchat Expert
02/10/2018 - 03:19
Yes sure.
Excelchat Expert
02/10/2018 - 03:19
To do this we would be using a formula. Let me just try the formula on my end to make sure the formula I'll be giving you is accurate.
User
02/10/2018 - 03:20
ok
User
02/10/2018 - 03:20
can you use oct. 16 as the sample
User
02/10/2018 - 03:20
we will need 5 bus days from there
Excelchat Expert
02/10/2018 - 03:20
Sure. May I know where October 16 will be located in the sheet?
Excelchat Expert
02/10/2018 - 03:20
Which cell, I mean.
Excelchat Expert
02/10/2018 - 03:21
Because the formula to do this is the WORKDAY() function and this requires the start date and the number of days.
User
02/10/2018 - 03:21
Oct. 16 is at c10
User
02/10/2018 - 03:21
but once we make the formula I'' want it aplied to c4 through c20
Excelchat Expert
02/10/2018 - 03:22
No problem, the formula can easily be dragged.
Excelchat Expert
02/10/2018 - 03:22
May I know the expectd result for 10/16/2018?
User
02/10/2018 - 03:22
10/23
User
02/10/2018 - 03:22
should be 5 bus days after
Excelchat Expert
02/10/2018 - 03:23
Alright, then the formula in D10 should work for you.
Excelchat Expert
02/10/2018 - 03:23
That formula can be dragged downwards.
User
02/10/2018 - 03:23
great! what is that?
User
02/10/2018 - 03:23
can youtype it out for me so I can see it?
Excelchat Expert
02/10/2018 - 03:23
=WORKDAY(C10,5)
User
02/10/2018 - 03:23
hm when I typed that in my doc
User
02/10/2018 - 03:24
it turned to "43396"
Excelchat Expert
02/10/2018 - 03:24
Ah yes, I forgot to mention that.
Excelchat Expert
02/10/2018 - 03:24
You just need to set the formatting of that column to Date.
Excelchat Expert
02/10/2018 - 03:24
Highlight column D then press CTRL+1 and then select Date.
User
02/10/2018 - 03:24
oh wow it worked!
Excelchat Expert
02/10/2018 - 03:25
Great!
Excelchat Expert
02/10/2018 - 03:25
Glad to have helped!
User
02/10/2018 - 03:25
so to drag the formula
User
02/10/2018 - 03:25
will it automatically adjust so that 11 gets the date from 11 and 12 from 12 etc
Excelchat Expert
02/10/2018 - 03:25
Yes, the formula is smart enough to automatically adjust when you drag downwards.
User
02/10/2018 - 03:25
can you drag it up?
Excelchat Expert
02/10/2018 - 03:26
Yes you may, it will also work.
User
02/10/2018 - 03:26
thank you. and if instead of adding 5 business days I wanted to take away business days, what do i do to the formula to make it subtract instead of add?
Excelchat Expert
02/10/2018 - 03:26
By the way, the formula will only cover the business days, it does not know if a certain date is a Holiday.
User
02/10/2018 - 03:26
ok thank you
Excelchat Expert
02/10/2018 - 03:27
And yes, -5 would work as well.
User
02/10/2018 - 03:27
thank you so much
Excelchat Expert
02/10/2018 - 03:28
If you need it to account for holidays then you will need to list down the holiday dates somewhere in the workbook.
User
02/10/2018 - 03:28
ok and i can make the formula 'ignore' those dates omehow?
Excelchat Expert
02/10/2018 - 03:28
Then add in the formula where you wrote the list.
Excelchat Expert
02/10/2018 - 03:29
FOr instance this formula:
Excelchat Expert
02/10/2018 - 03:29
=WORKDAY(C10,5,G2:G3)
Excelchat Expert
02/10/2018 - 03:29
Means it will ignore those dates listed in G2:G3
Excelchat Expert
02/10/2018 - 03:29
As you can see in the sheet to the right, the formula adjusted by 2 days because both holidays listed in G2:G3 are within the date we are working on.
User
02/10/2018 - 03:29
that's really helpful, thank you
Excelchat Expert
02/10/2018 - 03:30
You are welcome.
Excelchat Expert
02/10/2018 - 03:30
Would there be anything else that I can help you with regards to the original question?
User
02/10/2018 - 03:30
I think that is it, thank you
User
02/10/2018 - 03:30
if I need help in the future, can i log in again?
Excelchat Expert
02/10/2018 - 03:30
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
02/10/2018 - 03:31
Of course, you can always post your question again. :)
User
02/10/2018 - 03:31
thanks
User
02/10/2018 - 03:31
is this always free?
Excelchat Expert
02/10/2018 - 03:31
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
User
02/10/2018 - 03:31
thanks, i will rate you full stars
Excelchat Expert
02/10/2018 - 03:31
Oh, the first session is free but there are plans you can subscribe to.
Excelchat Expert
02/10/2018 - 03:31
Thank you I'd appreciate that. It would help me a lot. Please do not forget to click the End Session button so you can rate me.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
