Haven't properly used Excel in years but trying to make a spreadsheet based rota that I can give to my managers to create a basic rota quickly on the computer and it work out everything I need.
The reason I seem to have made it so complicated is because I'd like it to accept multiple ways of inputting the time such as "0500" or just "5" or "515", we only start on the hour or every fifteen minutes, so please excuse my hideous coding behind that bit. I also need it to calculate the hours per shift and I've got that working but now need to fit the code into one cell and I'm completely baffled how I'm going to do that.
So I'll show you what I've done:
A1: 415-1015
A2: 415
A3: 1015
A4: 4.25
A5: 10.25
A6: 6
Now rather than having this calculation taking up several cells for just one shift as it's going to be built for 30 staff over a full 7 day week, so that's a lot of calculations that need to happen and that's just too messy. Basically the result I get in A6 just needs to go straight into A2 to work out their shift length and in turn will be used to calculate their weekly hours.
Just as another example of how the code works:
A1: 915-16
A2: 915
A3: 16
A4: 9.25
A5: 16
A6: 6.75
Any help would be appreciated.
The reason I seem to have made it so complicated is because I'd like it to accept multiple ways of inputting the time such as "0500" or just "5" or "515", we only start on the hour or every fifteen minutes, so please excuse my hideous coding behind that bit. I also need it to calculate the hours per shift and I've got that working but now need to fit the code into one cell and I'm completely baffled how I'm going to do that.
So I'll show you what I've done:
A1: 415-1015
A2: 415
Code:
=LEFT(A1,SEARCH("-",A1,1)-1)
Code:
=RIGHT(A1,LEN(A1)-SEARCH("-",A1,1))
Code:
=IF(RIGHT(A4,2)="15",LEFT(A4,LEN(A4)-SEARCH("",A4,2))&".25",IF(RIGHT(A4,2)="30",LEFT(A4,LEN(A4)-SEARCH("",A4,2))&".50",IF(RIGHT(A4,2)="45",LEFT(A4,LEN(A4)-SEARCH("",A4,2))&".75",A4)))
Code:
=IF(RIGHT(A5,2)="15",LEFT(A5,LEN(A5)-SEARCH("",A5,2))&".25",IF(RIGHT(A5,2)="30",LEFT(A5,LEN(A5)-SEARCH("",A5,2))&".50",IF(RIGHT(A5,2)="45",LEFT(A5,LEN(A5)-SEARCH("",A5,2))&".75",A5)))
Code:
=A6-A5Now rather than having this calculation taking up several cells for just one shift as it's going to be built for 30 staff over a full 7 day week, so that's a lot of calculations that need to happen and that's just too messy. Basically the result I get in A6 just needs to go straight into A2 to work out their shift length and in turn will be used to calculate their weekly hours.
Just as another example of how the code works:
A1: 915-16
A2: 915
A3: 16
A4: 9.25
A5: 16
A6: 6.75
Any help would be appreciated.