Formula to change work schedule from month to month

**ibtrue** · 03-29-2010, 12:49 AM

Can anyone please help me?

I am responsible for making the monthly employee work schedule. Currently, I now save a copy of the current month and change the file name to the next month. Then I go in the sheet and change the month and the days of the week on one row then the corresponding date on the row underneath.

I also have to delete all the employees work schedules each day because that dated column no longer is valid for the next month as it belonged to the current month. Is there any shortcut to my prehistoric method. I just know there is!

Please help if you can,

ib

**teylyn** · 03-29-2010, 01:16 AM

posting a sample workbook might help. Replace all sensitive data with dummy data and provide a "before" and "after" scenario.

cheers

**ibtrue** · 03-29-2010, 01:54 AM

Here you go Teylyn

Thanx

**teylyn** · 03-29-2010, 04:55 AM

Hi, as a starting point:

do you always work in Page Preview? Normal view is much easier on the eyes, I feel.

Enter the first of the month in A1, i.e. 1/1/2010. Then in B1 enter

=A1

and then in C1

=B1+1

and copy across. This way, you can fill your day columns automatically

Format A1 with custom format "mmmm"
Format B1 to AF1 with custom format "d"

This will then show the numbers you are seeing now.

Whenever a new month rolls along, just change the date in A1 and away you go.

The secondary sheets can take their date from RN!A1

In row 2 enter in A2

=A1

in B2 enter

=LEFT(TEXT(B1,"ddd"),2)

and copy across

I don't know what the significance of the red formatting is. You'd need to explain.

Then, fire up the macro recorder, select cell B3, hit Ctrl-Shift-downArrow-RightArrow, delete key. Stop the macro recorder.

Now you have a macro that deletes the core contents of the sheet.

For months with less than 31 days, delete the last two days in the first sheet.

In the next month, just copy the leftmost cell to the right for a cell or two.

cheers