Put shift times into category columns

[tezza]

Hi All,

I'm working on a sheet that breaks down working shift patterns into categories:

Col G to L needs to populate automatically based on certain rules:

Col B must first me checked to see if the date is a Bank Holiday (so check a list eg: Cell N9 down for dates), if so then it's set at Weekend rate, however, if it goes past midnight then the remaining hours goes into the column based on Col E (so if Row 6 went past midnight it would look at Col E and put the extra hours into Col H), the same rule applies if working the day before a bank holiday into a bank holiday eg: 24/12 into 25/12

Weekend Rate starts at 00:00 Sat and ends 00:00 Mon
Therefore, Row 2 starts on a Friday at 10pm and works into Sat until 3am so so 2 hours would go into Col G based on the rate in Col E then switch to Col K Weekend rate
Same principle in reverse for Row 3 as the shift starts on the weekend but ends in monday - weekday (I've just seen a typo, D3 should be 4am not 16:00)

ST rate has it's own weekday and weekend rate as in row 4

I'm using formulas right now as I now very, very basic vba so that's getting complicated but I can't figure out the weekend to weekday split, same with bank holidays

The auto filled areas need to round to .25 - in formula I used Ceiling(G2,.25) as an example

The rate list needs to be dynamic so that different rates can be added, I'd have to add a new column to show that most likely.

Priority List:
Bank Holiday hours go into either Weekend or Weekend ST depending on Col E Rate
Weekend or Weekend ST hours same as above
Any hours that fall outside Bank Holiday or Weekend Hours fit into their own Col (Using midnights as a time cutoff)

Anyone fancy trying to set this up for me please?

Kind regards
Terry

 

[HighAndWilder]

You seem to have done most of it so far.

Re: I'm using formulas right now as I now very, very basic vba so that's getting complicated but I can't figure out the weekend to weekday split, same with bank holidays

To ascertain that the date is a weekday or a weekend use the WEEKDAY function. It returns a number 1-7 based upon the day of the week. E.g If you decide that Sunday is the first day of the week than 1 and 7 will be weekend days and 2 to 6 will be weekday. You can test for these numbers in other formula.

To determine if it a Bank Holiday you will need to maintain a separate worksheet of Bank Holiday dates. It only needs one column.
The formula =COUNTIF(BankHolidays!$A:$A,Timesheet!B4) will return 1 or 0 depending on whether the date is a BH or not. BankHolidays is the worksheet
containing the list of Bank Holidays and C4 is the date on the Timesheets worksheet.

You can use hidden columns which help you to break down the calculations. You can amalgamate the formulas later when you have
got it to work.

Are you ok with incorporating these ideas?

Are the values in column E manually entered?

 

[tezza]

You seem to have done most of it so far.

Re: I'm using formulas right now as I now very, very basic vba so that's getting complicated but I can't figure out the weekend to weekday split, same with bank holidays

To ascertain that the date is a weekday or a weekend use the WEEKDAY function. It returns a number 1-7 based upon the day of the week. E.g If you decide that Sunday is the first day of the week than 1 and 7 will be weekend days and 2 to 6 will be weekday. You can test for these numbers in other formula.

To determine if it a Bank Holiday you will need to maintain a separate worksheet of Bank Holiday dates. It only needs one column.
The formula =COUNTIF(BankHolidays!$A:$A,Timesheet!B4) will return 1 or 0 depending on whether the date is a BH or not. BankHolidays is the worksheet
containing the list of Bank Holidays and C4 is the date on the Timesheets worksheet.

You can use hidden columns which help you to break down the calculations. You can amalgamate the formulas later when you have
got it to work.

Are you ok with incorporating these ideas?

Are the values in column E manually entered?

Ah Countif, yes I can use that, but like the weekend I need to establish for how many hours like the row 2 & 3 examples, any suggestions?

Col E is auto populated from another sheet of data.