Timesheet Recurrence

Hello Everyone,

Not sure if this is the correct place to post this.

I am doing a timesheet for work and we do a 5 week rota.

I have a Shifts worksheet which includes all the shifts and days and hours we do I also have 12 sheets with columns that take the data from the shifts sheet.

What I am trying to do is to add the correct shifts to the responding dates of the month in each month of the year so it rolls over each month.

Hope you understand.

Kind Regards,

Mark

Mark,
Please post a small sample file so people have data to work with.

To upload a file, click on "Go Advanced" then the Paper Clip icon.

Book2 - Copy.xlsx

Hope this has worked

Hello Everyone,

Just rearranging my question.

I am doing a timesheet for work and we do a 5 week rota.

Within the file there is a Shift worksheet which includes all the shifts and days and hours we do also within the file there are 12 sheets which are the months from april to march these include the columns that the data from the Shift worksheet.

At the moment I have to enter the shift details by hand.

What I am trying to do is to add the correct shifts to the correct dates of the month bearing in mind we do a 5 week rota so some weeks have to be carried over to the next month.

I was wondering if there was code that could be added to each line in each month to make the data roll on and on.

Hope you understand.

Kind Regards,

Mark

timesheet-copy (1).xlsx

Thank You this works fine.

I just have 1 more question: - In the contracted hours column where the hours are showing 6.0, 7.0, 9.0 is it possible for them to just show 6 & 7 & 9 automatically or do they have to be changed manually by using the decrease decimal button the others are fine showing 11.5, 9.5.

Just format to General. Mea Culpa: I changed it in the April sheet It is General in the other sheets.

If you are happy with the solution please mark as SOLVED.

Thank you

I take it you have to do it manually i've tried changing the column in the shifts column but it does not change the sheets.

Also just been testing the change if I change the year in the shifts to april 2016 - 2017 the shifts don't change they stay as the previous year.

You need to change value in K1 to reflect when the new shift patterns start.

For 1 April 2015 the first shift was starting at row 31 (col j) so K1=30 and is increments by 1 in K2 onwards.

For April 1st 2016 the starting row is 12 as this is next shift entry after 31st march 2016 (shift pattern) so k1=11

You will see all the values change to reflect the new pattern.

I checked March 2016 and my calculated shift patterns don't appear to match your manually entered ones: is my calculation right? Logical it appears so to me - cycle through blocks of 35 days.

If I am correct, you should enter the row for 31/03/2016 i.e. 27 into K1 which gives the (continuation) for April 1st 2016 onwards i.e. 28 for 1st April 2016.

My previous post was based on looking at your manual entries: it then occurred to me to use the above.

To change the sheets you need set Column A/B (?) - see note below - to a date formatted as "d" and copy the formulas and drag down the columns.

I also note April has column A blank but May onwards start in A: I assume you have adjusted sheets/formulae accordingly.

With adjusting K1 in shifts the changes work from between april 2016 - march 2017 to the correct working pattern.

Regarding this question: - [In the contracted hours column where the hours are showing 6.0, 7.0, 9.0 is it possible for them to just show 6 & 7 & 9 automatically or do they have to be changed manually by using the decrease decimal button the others are fine showing 11.5, 9.5.] yes I can change the columns to general but this means I have to do every sheet just changing the sheets to the general format doesn't change all the sheets is there code to be added to the code in the sheets.

timesheet-copy (1).xlsx

Is there a way that a cell can add the contracted hours up that are booked off for a holiday.

Please look at the attached file I have highlighted a column of hours to add and place in a separate cell.

I assumes this sum based on "L" in column? if so use SUMIF

=SUMIF($AK$13:$AK$44,"L",$J$13:$J$43)

Any cells formatted as general will remain so: you don't need to (re)format.

Timesheet.xlsx

Hi,

Please take a ;look at the attached file.

The code works fine I just have to place it somewhere.

The other problem is on the SHIFTS sheet I have formatted the cells that have 6.0 & 7.0 in so they are showing 6 & 7 but on all the 12 sheets they do not change they are still stay showing 6.0 & 7.0 doesn't they formatting copy across with the functions that are in place.

I think depends on how you copy: if you highlight cell in April , run cursor across it to highlight the formula, copy, then paste the general formatting remains (or does with me).

At end of the day, you only have to highlight 12 columns and reformat to general: not too onerous? Took me all of 2 minutes!

I think I have done as much as can.