Put shift times into category columns

tezza

Board Regular
Joined
Sep 10, 2006
Messages
223
Office Version
  1. 2010
Platform
  1. Windows
Hi All,

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

data image.png


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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

HighAndWilder

Active Member
Joined
Nov 4, 2006
Messages
260
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 10, 2006
Messages
223
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,243
Messages
5,836,205
Members
430,406
Latest member
pmav

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top