Solved Excel help in adding days (POST 2)

June 2, 2015 at 03:53:39
Specs: Windows 7

Hi... i needed a help with this quesry

Is there a way where i can keeping adding days one after the other automatically.
I am not sure how to put it in words.
What i meant is :

Suppose the first column is a Sunday ... the second column we put a formula which gives me Monday and so on.
And if i change the first column to Tuesday it should automatically change the next column to Wednesday.

Please let me know if this is not clear.

A help will really be appreciated.



See More: Excel help in adding days (POST 2)

Report •


#1
June 2, 2015 at 05:01:52
see here.. You just type monday in a cell and with small black plus mark you drop down to get the days automatically
http://www31.zippyshare.com/i/MdL2y...

Report •

#2
June 2, 2015 at 06:26:43
✔ Best Answer
While Dashing_star's suggestion will work, you will have to use the Autofill handle each time you change the Day name in your first cell. If you are looking for a formula method to automatically update your list, try this:

1 - Enter a full Day name in A1 e.g. Sunday, Monday, etc
2 - Enter this formula in B1:

=TODAY()+8-WEEKDAY(TODAY()-MATCH(A1,
{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0))

3 - Depending on how B1 is formatted, that formula will probably return a Date, not a Day name. To fix that, Format the cell as Custom - and enter dddd in the Type box. The cell should now show the Day name that follows the Day name in A1.

4 - In C1 enter this formula and drag it as far you need:

=B1+1

Once again, you may have to format C1 and beyond as Custom - dddd

Now, each time you change the Day name in A1, the list in B1 and beyond will automatically update.

The reason this works is because even though the Day name in A1 is Text, the formula in B1 actually returns a Date. Since Excel sees the value in B1 as a Date (even if it is formatted as a Day name) you can add one to it to get the next Date. Further, since Excel knows what day of the week each Date is, formatting the cells as Custom - dddd will return the Day names.

Note: You might want to consider using a Data Validation Drop Down in A1 with a list of Day names to choose from. This will eliminate the chance of a typographical error. If the Day name in A1 is not spelled correctly, the CHOOSE function in B1 will fail and all of the "Day name" cells will display an error.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
June 2, 2015 at 08:55:14
This is a double post, See

http://www.computing.net/answers/of...

Did not know it was a double post until after I answered.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 2, 2015 at 09:04:23
Mike,

If V08ip is satisfied with the solution I offered in #2 above, I will delete the other thread.

As far as I can tell, v08ip is manually entering a "Day name" in his first cell, not a Date formatted to display the Day name. As I'm sure you know, no simple Date function or "A1+1" is going to return the next Day name based on a text entry of e.g. Monday.

You are much stronger with Time and Date functions than I am, so if you know of a simpler (single step?) method than I offered in #2 above, I'm all for it. :-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#5
June 2, 2015 at 12:59:24
DerbyDad03

v08ip is manually entering a "Day name" in his first cell

If you enter Monday or Mon in cell A1
then grab the fill handle and drag across,
you should get Tuesday or Tue in the next cell.

It is part of the Auto Fill feature.

MIKE

http://www.skeptic.com/


Report •

#6
June 2, 2015 at 13:10:08
Yes, Dashing_star suggested the Auto Fill feature in Response #1, but as I pointed out in #2, that requires a manual Auto Fill each time the first Day name is changed. My suggestion changes all of the "next days" automatically as requested by V08ip.

If he is only looking for a single "next day" the formula I suggested for B1 is all that he needs. If he is looking for multiple "next days", dragging B1+1 should get him as many sequential next days as he needs, automatically.

As always, we'll have to wait for the OP to let us know which one fits his needs.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question