Overly Complicated Excel Calculation.

Pulse · 21 Oct 2016 at 08:50

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

Code:

=LEFT(A1,SEARCH("-",A1,1)-1)

A3: 1015

Code:

=RIGHT(A1,LEN(A1)-SEARCH("-",A1,1))

A4: 4.25

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)))

A5: 10.25

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)))

A6: 6

Code:

=A6-A5

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.

Grrrrr · 21 Oct 2016 at 11:42

Assume we have a time in one of the following formats "0500" or "5" or "515" in cell A1

First we want to modify the text so they all show minutes in the right 2 characters

Code:

=A1&IF(LEN(A1)<3,"00","")
Result: 0500 or 500 or 515

Now lets make the number look like a time:

Code:

=TEXT(A1&IF(LEN(A1)<3,"00",""),"#0\:00")
Result: 05:00 or 5:00 or 5:15

Now it looks like a time we tell Excel it is a time:

Code:

=TIMEVALUE(TEXT(A1&IF(LEN(A1)<3,"00",""),"#0\:00"))
Result: 0.2083 or 0.2083 or 0.2187

But your question says the text actually has a start and finish time so you need to split them out. Assume A1 says: "415-1015"

Code:

[COLOR="Orange"]Start time:[/COLOR]
=LEFT(A1,SEARCH("-",A1,1)-1)
result: 415

[COLOR="Yellow"]Finish time:[/COLOR]
=MID(D17,SEARCH("-",D17,1)+1,100) [COLOR="SeaGreen"](this is slightly shorter than your method)[/COLOR]
result: 1015

So now we can plug these into the formula above (finish - start) to give:

Code:

=[COLOR="Yellow"]TIMEVALUE(TEXT(MID(D17,SEARCH("-",D17,1)+1,100)&IF(LEN(MID(D17,SEARCH("-",D17,1)+1,100))<3,"00",""),"#0\:00"))[/COLOR]-[COLOR="Orange"]TIMEVALUE(TEXT(LEFT(A1,SEARCH("-",A1,1)-1)&IF(LEN(LEFT(A1,SEARCH("-",A1,1)-1))<3,"00",""),"#0\:00"))[/COLOR]

That gives you the time worked in excel time format. If you want it in human readable format then:

Code:

=TEXT(<enormous formula>,"[h]:mm")

That gives you the answer in a single cell. There might be a shorter way of doing it but that's the best i can do. Also it won't work if someone finishes a shift after midnight.

I'd definitely suggest splitting it across more than one cell though. Have a "start" column and a "finish" column even if they're hidden. It'll be a much simpler formula to understand then.

Overly Complicated Excel Calculation.

Pulse

Pulse

Grrrrr

Grrrrr