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

Do older people you know use the Internet?

Discuss in The Lounge

Poll History