How to compute date changes for multiple cells in a row

[jarhtmd]

I'm using Excel 2007. I have a worksheet with E3 thru K3 (formatted general) that contain literals 8/11 Sun, 8/12 Mon, 8/13 Tue 8/14 Wed, 8/15 Thu, 8/16 Fri, 8/17 Sat. The 3-character days are always in the same columns. Actually, that row is repeated several rows down for 8/18 Mon thru 8/24. I copy the worksheet every 2 weeks and have to manually change those mm/dd day to different mm/dd. It's tedious, time consuming and error prone.

Is there a way to automate these changes using formulas, functions or some other "Excel magic"?

Thus far I've only used 1-digit months and 2-digit days, with leading zero for 1-digit days. Changing the column widths slightly and using 2-digits for both months and days will be fine. Months changing midweek won't be a problem. I can easily handle that manually.

I prefer changing only 1 row at a time since the row locations may change over time.

As evidence that I'm brain dead . . . I've tried multiple times to upload a sample, but can't get it to work. I choose a file, but can't upload. I've tried .xlsx and .jpg

Here's the best I can do 8/11 Sun, 8/12 Mon, 8/13 Tue, 8/14 Wed, 8/15 Thu, 8/16 Fri, 8/17 Sat

I want 8/18 Sun, 8/19 Mon, 8/20 Tue, 8/21 Wed, 8/22 Thu, 8/23 Fri, 8/24 Sat

[Pete_UK]

You can enter a date into E3 and apply a Custom Format to that cell of:

m/dd ddd

Then in F3 you can use this formula:

=E3+2

and use the same Custom Format. Then you can copy F3 across into the cells G3:K3

Use this formula in E8:

=K3+1

and in F8:

=E8+1

Copy this across.

Then, each time you need to set this up for a new fortnight you only need to change the date in E3.

You also need to use this formula in the merged cell A1:D1:

="Meds Schedule "&TEXT(E3,"mmmdd")&"-"&TEXT(K8,"mmmdd")

Hope this helps.

Pete

[jarhtmd]

Thanks,Pete,for your reply, but my problems started immediately when I tried to follow.

I changed the dates so I would start completely from scratch. The label in row-1 is a literal just for this try.

I populated & formatted E3 and 'copy...Paste special...formats only' into F3 thru K3, but
when I entered the formula =E3+2 I got the results below.

Just curious, but why F3 =E3+2 (instead of +1)?

[bebo021999]

It seems like you entered the date as text, not as an actual date.

Try again:

1- Enter the actual date 09/08/2024 into cell E3, and then format it as "d/mm ddd".
2- Copy the cell E3 to cells F3 and E8.
3- In cell F3, enter: =E3+1. Then copy it to the right.
4- In cell E8, enter: =E3+7. Then copy it to the right.

[Pete_UK]

... Just curious, but why F3 =E3+2 (instead of +1)? ...

Sorry about that - it should have been 1 and not 2.

If you try again to attach a file (by following the guidelines in the second yellow banner at the top of the screen) then we can make the necessary changes for you and post it back to you.

Hope this helps.

Pete

[AliGW]

Administrative Note:

Is your forum profile showing the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[jarhtmd]

Thanks Pete_UK and bebo021999, for all your help. I'm almost there. I would like for the dates to "Wrap Text"
. . . mm/dd
. . . . day with day of week shown under mm/dd, as shown on my attachments. Can I fix that by changing the Custom Format? That will allow printing in portrait mode on 8.5 x 11 paper.

AliGW, yes Excel2007 is the only version of Excel that I have. I know it's old, but so am I. I'm forgetting how to do things in Excel that were once 2nd nature for me. I see no reason to buy a new version that has many features that I would need to learn.

[AliGW]

You might want to add " ... in 2024" to your profile, otherwise you'll keep getting asked.

[Pete_UK]

Use this as the Custom Format string:

mm/dd^ddd

where the character ^ means you should do CTRL-J, i.e. hold down the CTRL key at that point and press the J key. This will insert the special character meaning line-feed, and with the cell set to Wrap text you should see the date appearing on two lines like this:

08/28
Wed

Note that there is a limit as to how narrow you can make this column before it starts to show ### - if this is too wide then perhaps you can set the font size smaller. You can use the Format Painter icon to apply the same format to the other cells.

You could also use a TEXT function which would enable you to make the columns even narrower, but let's see how this goes for now.

Any chance of adding a sample workbook, then we could try things out before returning it to you?

Hope this helps.

Pete

[Pete_UK]

I went ahead and set this up for you in the attached workbook, showing how you can achieve a narrower column of dates by using a TEXT function. First of all, you will notice that I have used cell M1 to set the start date for this fortnight. You can , of course, set the background colour of this cell to white, so that the cell appears to be blank, but I have put this in a column which is outside the Print Area (up to column K), so it will not show on a printout anyway.

The formula used in A1 is:

="Meds Schedule "&TEXT(M1,"MMMDD")&"-"&TEXT(M1+13,"MMMDD")

and this picks up the start date from M1 and adds 13 days to it to give the date range.

I have used this formula in cell E3:

=TEXT(DATE(YEAR($M$1+COLUMNS($E:E)-1),MONTH($M$1+COLUMNS($E:E)-1),DAY($M$1+COLUMNS($E:E)-1)),"mm/dd ddd")

and this can be copied across to K3. This does not need a Custom Format applied to the cell, as that is achieved by the TEXT function, and this means that a normal space can be used to separate the two parts. Adjusting the row height and setting the cells to wrap text and reducing the width will give you the effect that you are looking for. The formula in E8 is very similar, i.e.:

=TEXT(DATE(YEAR($M$1+COLUMNS($E:E)+6),MONTH($M$1+COLUMNS($E:E)+6),DAY($M$1+COLUMNS($E:E)+6)),"mm/dd ddd")

where the -1 has become +6 for a week later.

In using it for another fortnight, you just have to change the date in M1 and the file will reflect this.

Hope this helps.

Pete

[jarhtmd]

Thanks, Pete
You're a gentleman and a scholar! You've solved my problem. I appreciate your time and effort. See attached final version.

I seem to have a new problem each time I add to this thread. Now I can't find how to mark it solved.

[jarhtmd]

Thanks, Pete

You're a gentleman and a scholar! You've solved my problem. I appreciate your time and effort. See attached final version.

I seem to have a new problem each time I add to this thread. Now I can't find how to mark it solved.

[Pete_UK]

I can see that you must have found how to mark the thread as Solved, so I'm glad to have helped you get there.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[LARS GULYAS]

Pete_UK and jarhtmd

Comments to post #9 and 10
You can also insert Line Feed by entering ASCII-code 10 where you want it.
Press Alt-key and enter 0010 on the numeric key pad.

When changing date in cell M1 from 2024-08-25 to e.g. 2024-09-08 something odd happend.
Cell E3:K3 E8:k8 all showed 00 for months, num-day and text-week ok.
Something strange with the date format for month.The date format "mm/dd ddd" looked ok.
After some thinking I realized that in excel 2007 I used capital letters in date format, so when changing
it to "MM/DD DDD" it worked ok. Lower case letters i used for "hh:mm:ss".
Is this something that other users of excel 2007 have noticed ??

formula in cell E3:
=TEXT(DATE(YEAR($M$1+COLUMNS($E:E)-1),MONTH($M$1+COLUMNS($E:E)-1),DAY($M$1+COLUMNS($E:E)-1)),"MM/DD DDD")


/LGS