Thread: Date Change MM/DD/YYYY & 3-Work Day Weekly Repeat

Please bear with my ignorance.

SOLVED

In MS-Excel 2007 dates seem to be limited to YYYY/MM/DD style, which I imagine there is a way to alter it into MM/DD/YYYY. I looked into MS Help, and a google search, and either I was using wrong keywords, or not, but I couldn't find anything to simply change date arrangement. If I am being unclear, I am talking about the function '=DATE(YYYY/MM/DD)' is what I want to change.

After I get that fixed, I need there to be a function to reproduce a 3-day work week (Mon, Tue, and Thur). e.g.:

10/05/09 [data] [data] [data]
10/06/09 [data] [data] [data]
10/08/09 [data] [data] [data]
Week 1 [data sum] [data sum] [data sum]

10/12/09
10/13/09
10/15/09
Week 2

10/19/09
10/20/09
10/22/09
....

And so on and so forth. I would have to experiment with the function, but I was thinking something that works with adding, per say, 6 cells above and adding 7 to the day listing (thus making it a week later). That would also I need (or hope) that the date function would automatically bump over to the next month.

Week 2 Week 3 ....
10/12(+7)/09 = 10/19/09 ....
10/13(+7)/09 = 10/20/09 ....
10/15(+7)/09 = 10/22/09 ....

And about the bump function, something like:
10/29(+7)/09 = 10/36/09
would automatically change to
11/05/09

I hope I am making sense here. I will do my best to rephrase anything that I can.

Thanks in Advance,
Jimmi G

I Can help with part of it.

The style of excel dates are partley based on the system setup. If you go to contorl pannel, regional & language settings, custiomize, date you can change what is shown 1st, d/m/yyyy or m/d/yyyy ect.

In excel you can, home number, dropdown choose more number formats.. from there you can select a preset date style. or go custom and enter d/mm/yyy would show 1/1/2009. d/mmm/yy would show 1/jan/09, or d m yyyy would show 1 1 2009.

also =DATE(2009,1,1) would show 1/1/2009, (=date(year,month,day). =DATE(2009,1+1,1) whould show 1/2/2009


hope this helps you in some way

also if in A1 you have =DATE(2009,1,1).. A2, =A1+1... A3 =A1+2........
will show the three day setup based on cell A1.

Hi Jimmi G,

The Excel function to calculate a date has the syntax

=DATE(year, month, day)

You can not change the syntax of a function.

You can however, change how a date is displayed in a cell. This is done by formatting the cell. There are many in-built date formats available in Excel. Maybe you need to read up in the help how to format a date?

As to your sheet, it would be a lot easier to give advice based on a data sample, so if you could post a workbook, ....?

If you start out with a date like 10/05/2009 in cell A1, you can use this formula

=A1+1

to increase the date by one day. You can use

=A1+7

to increase the date by one week, and, yes, if you increase 09/30/2009 by one week by adding 7 days, it will come up with an October date.

If you have more specific questions, please click Go Advanced below, then click the paperclip icon to upload a workbook.

Make it small, just enough to show your data layout, where your input is, and what you want the output to be.

hth

Thanks a bunch guys! I've been horribly busy over the last week, and finally was able to try it out, and with success.