My works rota vs payroll efficiency spreadsheet

M

moggal

New Member
Joined
Dec 27, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hey all,

All over Christmas I've been trying to find a way to do this (sad i know) but have come up with zilch, my brain just wont let me find a way to do it so im hoping someone else could point me in the right direction.

My works rota spreadsheet (which all stores have to use) works in format of entering a 1 under each full hour worked, if you work half an hour in that hour (the image I've attached will explain it better)

This is time consuming and awful, im wondering is there any way (ideally without vba) that I can enter a start time and finish time (like on the left in the pic) and it to populate the correct format for my works rota spreadsheet (like on the right).

I think the way they so this is that it feeds into their payroll system and it has to be that format.

Any ideas? Im open to VBA solutions if that's the last resort, its just my works excel has VBA disabled so testing at work is a once a shift thing.

Thanks for any solutions
 

Attachments

  • Capture.JPG
    Capture.JPG
    50.1 KB · Views: 9

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
I think I got it (after 4 attempts and corrections)!
Book1
ABCDEFGHIJKLMNOPQRS
1PersonShift StartShift End6789101112131415161718192021
2Manager #106:3014:300.511111110.5       
3Manager #210:1516:45    0.25111110.75     
4Manager #309:0017:00   111111111    
5Manager #413:0021:00       111111111
6Manager #513:1521:45       0.2511111110.75
Sheet5
Cell Formulas
RangeFormula
D2:S6D2=IF(AND(HOUR($B2)=D$1,MINUTE($B2)<>0),ROUND(MINUTE($B2)/60,2), IF(AND(HOUR($C2)=D$1,MINUTE($C2)<>0),ROUND(MINUTE($C2)/60,2), IF(AND(HOUR($B2)<=D$1,HOUR($C2)>=D$1),1,"")))

I didn't put that into an Excel Table, but it's worth mentioning that making it one would mean the only input needed would be columns A-C.
This was easy enough to put together a sample, but please use XL2BB to post data or at least enter it into a table. Glad you have what version of Excel you're using, otherwise you might have gotten a solution you couldn't use!
 
Upvote 0
jdellasala said:
I think I got it (after 4 attempts and corrections)!
Book1
ABCDEFGHIJKLMNOPQRS
1PersonShift StartShift End6789101112131415161718192021
2Manager #106:3014:300.511111110.5       
3Manager #210:1516:45    0.25111110.75     
4Manager #309:0017:00   111111111    
5Manager #413:0021:00       111111111
6Manager #513:1521:45       0.2511111110.75
Sheet5
Cell Formulas
RangeFormula
D2:S6D2=IF(AND(HOUR($B2)=D$1,MINUTE($B2)<>0),ROUND(MINUTE($B2)/60,2), IF(AND(HOUR($C2)=D$1,MINUTE($C2)<>0),ROUND(MINUTE($C2)/60,2), IF(AND(HOUR($B2)<=D$1,HOUR($C2)>=D$1),1,"")))

I didn't put that into an Excel Table, but it's worth mentioning that making it one would mean the only input needed would be columns A-C.
This was easy enough to put together a sample, but please use XL2BB to post data or at least enter it into a table. Glad you have what version of Excel you're using, otherwise you might have gotten a solution you couldn't use!
Click to expand...

Hey Jerry

Thanks for the formula, its been frying my brain, the only thing thats not correct is the part hours should be how much of that hour theyve worked, so if the start time is 12:15 that hour should say 0.75 not 0.25, looking at the formula im unsure of how i would get that figure.

Also thanks for the tip on XL2BB :)

Book1
BCDEFGHIJKLMNOPQRST
306:00 - 07:0007:00 - 08:0008:00 - 09:0009:00 - 10:0010:00 - 11:0011:00 - 12:0012:00 - 13:0013:00 - 14:0014:00 - 15:0015:00 - 16:0016:00 - 17:0017:00 - 18:0018:00 - 19:0019:00 - 20:0020:00 - 21:0021:00 - 22:0022:00 - 23:00
4678910111213141516171819202122
5startend
612:1520:15      0.2511111110.25  
Sheet1
Cell Formulas
RangeFormula
D6:T6D6=IF(AND(HOUR($B6)=D$4,MINUTE($B6)<>0),ROUND(MINUTE($B6)/60,2), IF(AND(HOUR($C6)=D$4,MINUTE($C6)<>0),ROUND(MINUTE($C6)/60,2), IF(AND(HOUR($B6)<=D$4,HOUR($C6)>=D$4),1,"")))
 
Upvote 0
moggal said:
Hey Jerry

Thanks for the formula, its been frying my brain, the only thing thats not correct is the part hours should be how much of that hour theyve worked, so if the start time is 12:15 that hour should say 0.75 not 0.25, looking at the formula im unsure of how i would get that figure.

Also thanks for the tip on XL2BB :)

Book1
BCDEFGHIJKLMNOPQRST
306:00 - 07:0007:00 - 08:0008:00 - 09:0009:00 - 10:0010:00 - 11:0011:00 - 12:0012:00 - 13:0013:00 - 14:0014:00 - 15:0015:00 - 16:0016:00 - 17:0017:00 - 18:0018:00 - 19:0019:00 - 20:0020:00 - 21:0021:00 - 22:0022:00 - 23:00
4678910111213141516171819202122
5startend
612:1520:15      0.2511111110.25  
Sheet1
Cell Formulas
RangeFormula
D6:T6D6=IF(AND(HOUR($B6)=D$4,MINUTE($B6)<>0),ROUND(MINUTE($B6)/60,2), IF(AND(HOUR($C6)=D$4,MINUTE($C6)<>0),ROUND(MINUTE($C6)/60,2), IF(AND(HOUR($B6)<=D$4,HOUR($C6)>=D$4),1,"")))
Click to expand...
Yep. You're right! Luckily it's an easy fix (I HOPE!):
Excel Formula: 
=IF(AND(HOUR($B2)=D$1,MINUTE($B2)<>0),1-ROUND(MINUTE($B2)/60,2),
  IF(AND(HOUR($C2)=D$1,MINUTE($C2)<>0),1-ROUND(MINUTE($C2)/60,2),
  IF(AND(HOUR($B2)<=D$1,HOUR($C2)>=D$1),1,"")))
Just had to take the answer I had and subtract it from 1. Hope that does it. Pretty sure it does.
Book1
ABCDEFGHIJKLMNOPQRS
1PersonShift StartShift End6789101112131415161718192021
2Manager #106:3014:300.511111110.5       
3Manager #210:1516:45    0.75111110.25     
4Manager #309:0017:00   111111111    
5Manager #413:0021:00       111111111
6Manager #513:1521:45       0.7511111110.25
Sheet5
Cell Formulas
RangeFormula
D2:S6D2=IF(AND(HOUR($B2)=D$1,MINUTE($B2)<>0),1-ROUND(MINUTE($B2)/60,2), IF(AND(HOUR($C2)=D$1,MINUTE($C2)<>0),1-ROUND(MINUTE($C2)/60,2), IF(AND(HOUR($B2)<=D$1,HOUR($C2)>=D$1),1,"")))

Glad I didn't dump that yet.
 
Upvote 0
moggal said:
Hey Jerry

Thanks for the formula, its been frying my brain, the only thing thats not correct is the part hours should be how much of that hour theyve worked, so if the start time is 12:15 that hour should say 0.75 not 0.25, looking at the formula im unsure of how i would get that figure.

Also thanks for the tip on XL2BB :)

Book1
BCDEFGHIJKLMNOPQRST
306:00 - 07:0007:00 - 08:0008:00 - 09:0009:00 - 10:0010:00 - 11:0011:00 - 12:0012:00 - 13:0013:00 - 14:0014:00 - 15:0015:00 - 16:0016:00 - 17:0017:00 - 18:0018:00 - 19:0019:00 - 20:0020:00 - 21:0021:00 - 22:0022:00 - 23:00
4678910111213141516171819202122
5startend
612:1520:15      0.2511111110.25  
Sheet1
Cell Formulas
RangeFormula
D6:T6D6=IF(AND(HOUR($B6)=D$4,MINUTE($B6)<>0),ROUND(MINUTE($B6)/60,2), IF(AND(HOUR($C6)=D$4,MINUTE($C6)<>0),ROUND(MINUTE($C6)/60,2), IF(AND(HOUR($B6)<=D$4,HOUR($C6)>=D$4),1,"")))
Click to expand...
I just noticed that you went back to indicating the top row with From and To times. That will be unworkable, and besides it's redundant - as in the second number in the column is the first number in the next column. I used plain Integers for the header row (row 1), and I've been unable to find any custom format that might display them as hh:mm.
If you MUST use hh:mm, then every reference in the formula to Row 1 will need to be calculated as the HOUR of that value.
Book1
ABCDEFGHIJKLMNOPQRS
1PersonShift StartShift End06:0007:0008:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:00
2Manager #106:3014:300.511111110.5       
3Manager #210:1516:45    0.75111110.25     
4Manager #309:0017:00   111111111    
5Manager #413:0021:00       111111111
6Manager #513:1521:45       0.7511111110.25
Sheet5
Cell Formulas
RangeFormula
D2:S6D2=IF(AND(HOUR($B2)=HOUR(D$1),MINUTE($B2)<>0),1-ROUND(MINUTE($B2)/60,2), IF(AND(HOUR($C2)=HOUR(D$1),MINUTE($C2)<>0),1-ROUND(MINUTE($C2)/60,2), IF(AND(HOUR($B2)<=HOUR(D$1),HOUR($C2)>=HOUR(D$1)),1,"")))
 
Upvote 0
jdellasala said:
I just noticed that you went back to indicating the top row with From and To times. That will be unworkable, and besides it's redundant - as in the second number in the column is the first number in the next column. I used plain Integers for the header row (row 1), and I've been unable to find any custom format that might display them as hh:mm.
If you MUST use hh:mm, then every reference in the formula to Row 1 will need to be calculated as the HOUR of that value.
Book1
ABCDEFGHIJKLMNOPQRS
1PersonShift StartShift End06:0007:0008:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:00
2Manager #106:3014:300.511111110.5       
3Manager #210:1516:45    0.75111110.25     
4Manager #309:0017:00   111111111    
5Manager #413:0021:00       111111111
6Manager #513:1521:45       0.7511111110.25
Sheet5
Cell Formulas
RangeFormula
D2:S6D2=IF(AND(HOUR($B2)=HOUR(D$1),MINUTE($B2)<>0),1-ROUND(MINUTE($B2)/60,2), IF(AND(HOUR($C2)=HOUR(D$1),MINUTE($C2)<>0),1-ROUND(MINUTE($C2)/60,2), IF(AND(HOUR($B2)<=HOUR(D$1),HOUR($C2)>=HOUR(D$1)),1,"")))
Click to expand...
Hey

The from and to times are just a labels for other users as it mirrors the main spreadsheet we use, they arnt used in any formula. All the formulas will use the single digits as per your answer.

Thanks again :-)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
My formula is unnecessarily copied to the adjacent cell when entering data into another cell
Replies
6
Views
324
MrsGrayMarlin
M
W
  • Question Question
Formatting raw data with extra rows automatically added in-between records
Replies
1
Views
71
Peter_SSs
Peter_SSs
L
  • Question Question
One click solution for printing a column of .pdf links
Replies
5
Views
463
JEC
JEC
FrumpyJones
  • Question Question
Is there a way to stop COPY/PASTE but still allow manual entries and use of drop down lists.
Replies
5
Views
310
FrumpyJones
FrumpyJones
D
  • Question Question
Trying to force people to not use a comma (even when pasting) or enter a number in a specific format
2 3
Replies
22
Views
890
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

Share this page

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
Back
Top