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: 8

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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

AndyTampa
  • Solved
InputBox to ask for date and only accept standard date formats
2 3
Replies
27
Views
1K
AndyTampa
AndyTampa
H
  • Question
'How to' question about a Dashboard
Replies
5
Views
207
etaf
etaf
P
  • Solved
VBA - Userforms and Dates
Replies
3
Views
406
Pacman52
P
T
  • Question
"Compile Error: Out of memory" with complex userform and many vba subroutines
Replies
1
Views
202
toole123
T
E
  • Solved
Weird Excel 2021 Date Formatting Not Working
Replies
8
Views
185
Dave Patton
D

Forum statistics

Threads
1,214,915
Messages
6,122,212
Members
449,074
Latest member
cancansova

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