increment date by one day

**EAMOG** · 07-19-2008, 07:55 PM

Originally Posted by Ron Coderre

Perhaps something like this....
With
E10: (any date....eg 7/9/2008)

This formula returns the Monday date of the week containing the date in E10

E11: =E10-WEEKDAY(E10,2)+1

Next, to fill in the rest of the week:

F11: =+E11+1

Copy F11 across through I11

Last, make sure E11:I11 are formatted the way you want:
• Select E11:I11
• From the Excel Main Menu: <format><cells><number tab>
• Category: Custom
• Type: ddd, mm/dd <-(you'll need to type that part in)
• Click [OK]

In the above example, the formulas will return:

E11: Mon, 07/07
F11: Tue, 07/08
G11: Wed, 07/09
H11: Thu, 07/10
I11: Fri, 07/11

Is that something you can work with?

Hi was just looking through this thread and was quite interested..can you please explain in plain english what the last bit means...(E10,2 ETC....... E10-WEEKDAY(E10,2)+1[/CODE]

Thanks

**Ron Coderre** · 07-19-2008, 08:27 PM

With 
E10: (a date)
E11: =E10-WEEKDAY(E10,2)+1

WEEKDAY(E10,2) returns the weekday_number of the date in E10
where: Mon = 1, Tue = 2, Wed = 3, Thu = 4, Fri = 5, Sat = 6, Sun = 7

So...If you subtract a date's weekday_num from the date,
the result is the Sunday date prior to that date.

Adding 1 to that value is the Monday date for the week containing the date in E10.

Example:
E10: 07/15/2008.....(a Tuesday)
• WEEKDAY(E10,2)....returns: 2
• =E10-WEEKDAY(E10,2) 
  =07/15/2008 - 2 days
  =07/13/2008......(a Sunday)
• =E10-WEEKDAY(E10,2)+1
  =07/15/2008 - 2 days + 1 day
  =07/14/2008......(a Monday)

Note: You can designate which day you want the week to start on
in the WEEKDAY function by setting the 2nd argument value.
See Excel Help on that function for the details.

I hope that helps.

**emilis** · 07-22-2008, 06:43 PM

Hi, Ron -

I'm seeing this in other formulas that I'm coming across and am not sure why it's written this way. You used it above:

F11: =+E11+1

Why is there a + sign right after the = sign? Why not just write it

F11: =E11+1

When I plug it in, I'm not seeing a difference either way.

Curious,

Emil

**Ron Coderre** · 07-22-2008, 07:25 PM

You're correct...in that instance, there's no difference.
I don't routinely type the equal sign AND the leading plus sign (+).
It's probably there because I typed the plus sign then selected
cell E11 (and Excel put the equal sign in for me). Other times I may start
with the equal sign before selecting cells.

I hope that helps.

increment date by one day

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Explanation for: E11: =E10-WEEKDAY(E10,2)+1

=+Ref vs =Ref

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions