CODEWITHSUNDEEP
exceldateexcel-formula
DrCustUmz
DrCustUmz

Reputation: 388

Ms excel - how do I add 1 day to my custom date?

I'm trying to add days consecutively across row 9 starting with Sunday. In field e3 is the date for the beginning of the pay period in 6/24/17 format In field c9 I have =TEXT(E3,"ddd, m/dd") In d9 I have tried =TEXT(E3,"ddd, m/dd")+1 =C9+1 And a few other ways but it returns "value"

How can I get the days in my custom format to auto grow in the custom format I have based on the original date in e3?

Upvotes: 3

Views: 22320

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24430

Don't use TEXT to format a date. Rather, right click on the cell(s), select Format Cells, Number, Custom, and enter your custom format there.

To increment dates, simply doing =dateCell + 1 works so long as that cell is a date; if it's not you're attempting to add 1 to a string; which doesn't make sense.

Example

Create a sheet with the following values/ formulae:

  | A           | B              |
1 | Date        | Formatted Date |
2 | 2017-06-24  | =A2            |
3 | =A2 + 1     | =B2 + 1        |

Leave column A's formatting as General; Excel will figure out that it's a date and will display it according to your local settings.

In column B, follow the steps above to format the cells as ddd, M/dd.

The result should look like this (column A's behaviour depending on your regional settings):

  | A           | B              |
1 | Date        | Formatted Date |
2 | 06/24/2017  | Wed, 6/24      |
3 | 06/25/2017  | Thu, 6/25      |

Upvotes: 5

Related Questions