I will like to know how to add the working days of the week (5 days) to a date in excel. Also, how can holidays be factored in so that excel will not see it as a working day. Will these require a formula or formulas?

Any help will be appreciated, thanks.

✔ Best Answer

A little tip about posting in a forum such as this one: We can only respond to what you put in a post. We can't read you mind or see your spreadsheet.

In your first post you said you wanted to add days to a

date.Then is your latest post you used

A1 = FridayandA1 = Monday.Which is it? Do your cells contain dates or days of the week?

You also said:

I want to be use B1 to display what the next 4 working days will beThat sounds like you want B1 to display 4 days in one cell.

Let's assume what you really want is to know is the date of 4 working days after a given date. I already answered that question.

Did you look at the WORKDAY function in Excel Help as I suggested?

If A1 = 6/25/2010 (a Friday) then =WORKDAY(A1, 4) will return 7/1/2010 (a Thursday) because it will skip Saturday and Sunday.

If A1 = 6/28/2010 (a Monday) then =WORKDAY(A1, 4) will return 7/2/2010 (a Friday) because there is no Saturday or Sunday within 4 days after Monday.

If you really want to return the day of the week, they use:

=TEXT(WORKDAY(A1,4),"dddd")

If A1 = 6/28/2010 (a Monday) this will return Friday.

Hi, When you say

add the working days of the week (5 days)do you mean you just want to add 5 to a date, or do you want a list of 5 working days in 5 cells beneath a date.1. If cell A1 contains a date, in cell B1 enter =A1+5

2. If cell A1 contains a date, in cell A2 enter =A1

in Cell A3 enter =A2+1

drag cell A3 down to extend it to A6

Select cells A2 to A6 and format as "dddd" to show daysSomehow I think that you must be looking for more than this - so perhaps a bit more information would help.

Regards

Adding 5 days to a date is as simple as this: With a date in A1, use:

=A1+ 5

Since Excel counts "days" as "integers" and hours/minutes/seconds fractions of a day, each whole number added is a full day.

If you want to only include work days, look at the WORKDAY function in Excel Help.

WORKDAY(start_date,days,holidays) will return the date representing "days" number of days after the start_date.

If you provide a list of holidays in a range of cells, it will not count them as workdays.

With a date in A1, try:

=WORKDAY(A1,5)

Humar & DerbyDad, More info. I am trying to use this for scheduling.

I want to create a worksheet whereby I can put any working day in a cell and the next cell will automatically add 4 working days even if the day happens to fall on a Friday.

For instance, if A1 = Friday and I have 4 working days to turn in a report; I want to be use B1 to display what the next 4 working days will be. But since next 2 days will be Saturday & Sunday, how can I skip these two days? And how can I incorporate in the SAME worksheet if the next 4 workings are still in that week (for instance, if A1 = Monday) so that i won't have to keep adding formulas?

I hope I am a bit clearer.

Thanks for your help.

A little tip about posting in a forum such as this one: We can only respond to what you put in a post. We can't read you mind or see your spreadsheet.

In your first post you said you wanted to add days to a

date.Then is your latest post you used

A1 = FridayandA1 = Monday.Which is it? Do your cells contain dates or days of the week?

You also said:

I want to be use B1 to display what the next 4 working days will beThat sounds like you want B1 to display 4 days in one cell.

Let's assume what you really want is to know is the date of 4 working days after a given date. I already answered that question.

Did you look at the WORKDAY function in Excel Help as I suggested?

If A1 = 6/25/2010 (a Friday) then =WORKDAY(A1, 4) will return 7/1/2010 (a Thursday) because it will skip Saturday and Sunday.

If A1 = 6/28/2010 (a Monday) then =WORKDAY(A1, 4) will return 7/2/2010 (a Friday) because there is no Saturday or Sunday within 4 days after Monday.

If you really want to return the day of the week, they use:

=TEXT(WORKDAY(A1,4),"dddd")

If A1 = 6/28/2010 (a Monday) this will return Friday.

I am sorry that I was not that clear about my question. But your assistance have helped me to solve the riddle, I really appreciate your help. I used the Workday(a1,4) you had suggested and it worked really well.

Thanks

Ask Your Question

Weekly Poll