Solved How to add working days to a date in excel

Dell Optiplex 330 energy smart minitower...
June 28, 2010 at 07:07:29
Specs: Windows XP
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.


See More: How to add working days to a date in excel

Report •


✔ Best Answer
June 28, 2010 at 20:03:24
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 = Friday and A1 = 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 be

That 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.



#1
June 28, 2010 at 13:51:43
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 days

Somehow I think that you must be looking for more than this - so perhaps a bit more information would help.

Regards


Report •

#2
June 28, 2010 at 13:59:35
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)


Report •

#3
June 28, 2010 at 17:21:10
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.


Report •

Related Solutions

#4
June 28, 2010 at 20:03:24
✔ 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 = Friday and A1 = 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 be

That 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.


Report •

#5
June 29, 2010 at 05:03:26
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


Report •


Ask Question