Subtracting two numbers contained within one cell

[bl1nd]

I have a schedule that is in 24 hour clock and the cells are formatted using Custom 0000 to allow me to input the schedule without the use of the colon.

[TABLE="class: grid, width: 750"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]AC[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]Mon-Tue[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Shift
Length[/TD]
[TD="align: center"]Shift
Length[/TD]
[TD="align: center"]Time In
Between
Days[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]0600[/TD]
[TD="align: center"]1430[/TD]
[TD="align: center"]0700[/TD]
[TD="align: center"]1530[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]16.5[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1700[/TD]
[TD="align: center"]0130[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]1400[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]4.0[/TD]
[TD="align: center"]8.5[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1430[/TD]
[TD="align: center"]2300[/TD]
[TD="align: center"]0600-1000[/TD]
[TD="align: center"]1800-2200[/TD]
[TD="align: center"]8.0[/TD]
[TD="align: center"]#value![/TD]
[TD="align: center"]#value![/TD]
[/TR]
</tbody>[/TABLE]

In cells U4:V6 I have the following formula;

=IF(E4="","",IF(MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24)>5.4,MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24)-0.5,MOD(DOLLARDE(E4/100,60)-DOLLARDE(D4/100,60),24)))

This formula was created by JoeMo to subtract the Time In from the Time Out, taking into consideration that sometimes the Time Out value can be lower than the Time In (eg- cell E5 on Monday is past midnight). The formula also subtracts 0.5 from all results that are longer than 5.4 hours in length (for staff lunch breaks).

In cells AC4:AC6 I have the following formula;

<d4,mod(dollarde(f4 100,60)-dollarde(e4="" 100,60),24),mod(dollarde(f4="" 100,60),24)+if(f4=""><d5,mod(dollarde(f5 100,60)-dollarde(e5="" 100,60),24),mod(dollarde(f5="" 100,60),24)+if(f5="">=IF(OR(E5="",F5=""),"",IF(E5[I][COLOR=#0000cd]*less-than-symbol*[/COLOR][/I]D5,MOD(DOLLARDE(F5/100,60)-DOLLARDE(E5/100,60),24),MOD(DOLLARDE(F5/100,60)-DOLLARDE(E5/100,60),24)+IF(F5>=E5,24,0)))

A very similar formula also created by JoeMo in the same thread I linked to above, this calculates the time in-between two shifts / days by subtracting Mondays Out time from Tuesday In time, also taking into consideration that some shifts will be ending before midnight, and some after.

So far, this is all working amazing! The only issue I am coming into is that sometimes staff are required to work a split shift - ie two shifts on the same day.
Right now, I am entering this as shown in cells F6:G6 as this is the easiest for the staff to understand, however this obviously throws all of my formulas out of the window as they cannot make sense of it!

I have done some extensive digging around looking for a solution and the closest thing I can find is this thread;
http://www.mrexcel.com/forum/excel-...ple-numbers-contained-within-single-cell.html

Does anyone know of a way to incorporate this into my formulas? Or perhaps a completely different way of going about finding a solution such as a VBA button that adds extra rows or something?

I have over 70 staff and there is almost always someone doing a split shift once a week so finding a solution to this issue is essential to my entire spreadsheet working (It is currently done manually (pencil & paper) by my boss & I am trying to roll out this spreadsheet in replacement).

ps- </d5,mod(dollarde(f5></d4,mod(dollarde(f4>*less-than-symbol* - The less than symbol does not show up on this forum for me, when I paste it in, it shows until I preview the post and then it deletes it along with the next 20 or so characters :/ very strange

 

[bl1nd]

You rock! Looks like I am finally there, thank you so much for your continued help!!!!
*high five*
:D

 

[bl1nd]

The timeBetween function also contained the logic that if the 2nd arg < 1st arg it's the next day, but it's always the next day
You can apply that change yourself. Near the end of the timeBetween function change the line

    If shift2Begin < shift1End Then shift2Begin = shift2Begin + 1

to

    shift2Begin = shift2Begin + 1

(remove the condition)

I misunderstood you...
Using your solution above works for shifts that finish earlier than the next one starts, except when they finish past midnight.
So if a shift on Monday is 1630 - 0100 and then the shift on Tuesday starts at 0800, it should return 7.
I know this is a complicated one, but do you see there being a work around? I know I had issues with getting this to work in a formula, but we were eventually able to come up with the formula below that worked for both situations.


=IF(OR(E4="",F4=""),"",IF(E4< D4,MOD(DOLLARDE(F4/100,60)-DOLLARDE(E4/100,60),24),MOD(DOLLARDE(F4/100,60)-DOLLARDE(E4/100,60),24)+IF(F4>=E4,24,0)))

 

[ask2tsp]

Did a few tests, made a few changes. Code now looks like

Function timeBetween(timeEnd As Range, timeBegin As Range) As Single
'calculates the time from the end of one shift to the start of the next shift
    Dim tBeg        As String
    Dim tEnd        As String
    Dim pos         As Integer
    Dim shift1End   As Single
    Dim shift2Begin As Single
    
    tBeg = timeBegin.Text
    tEnd = timeEnd.Text
    
    If tBeg = "" Or tEnd = "" Then  'if not both specified
        timeBetween = 0             'return 0
        Exit Function
    End If
    
    pos = InStr(1, tBeg, "-")
    If pos > 0 Then
        shift2Begin = num2time(--Left(tBeg, pos - 1))
    Else
        shift2Begin = num2time(--tBeg)
    End If
    
    pos = InStr(1, tEnd, "-")
    If pos > 0 Then
        shift1End = num2time(--Mid(tEnd, pos + 1))
        shift1Begin = num2time(--Left(tEnd, pos - 1))
    Else
        shift1End = num2time(--tEnd)
        shift1Begin = num2time(--timeEnd.Offset(, -1))
    End If

    'if shift ends on same day as it started
    'the next shift starts on the next day
    shift2Begin = shift2Begin - (shift1End > shift1Begin)
    
    timeBetween = (shift2Begin - shift1End) * 24
End Function

Please replace the timeBetween function with this update

 

[bl1nd]

Seams to work great, thanks again!

 

[bl1nd]

Is there a way to have shiftLength return nothing ("") instead of 0?
It is now messing up one of my tick-box conditional formatting that highlights shift lengths that are below the average for the day as it displays all the 0's as below average, but when it returns nothing it excludes them.

If not, no big deal!

 

[ask2tsp]

See post #18 on the conditional format.

 

[bl1nd]

See post #18 on the conditional format.

I used that (& it worked great) for the conditional formatting that was applied to the schedule, however, this is for a conditional format that is applied to the shift length calculations on the right of the main schedule. It uses the built in function of highlighting values that are below average, not a formula, so I cannot use the same tip to fix it...

 

[ask2tsp]

I made the next two minor changes to the shiftLength function:

1. Change the return type of the function

Function shiftLength(timeBegin As Range, timeEnd As Range) As Variant

2. Return empty string when there's no value (your initial request)

    If tBeg = "" Or tEnd = "" Then  'if not both specified
        shiftLength = ""             'return empty string
        Exit Function
    End If

Neat huh?

 

[bl1nd]

I made the next two minor changes to the shiftLength function:

1. Change the return type of the function

Function shiftLength(timeBegin As Range, timeEnd As Range) As Variant

2. Return empty string when there's no value (your initial request)

    If tBeg = "" Or tEnd = "" Then  'if not both specified
        shiftLength = ""             'return empty string
        Exit Function
    End If

Neat huh?

I tried before replacing the 0 with "" as you did in

If tBeg = "" Or tEnd = "" Then 'if not both specified
shiftLength = "" 'return empty string
Exit Function
End If

and it didn't work for me, but works this time :D

Thanks!

 

[ask2tsp]

That's why I changed the function type to Variant, which is a, as the name suggests, a variable data type, that adapts to what you feed it.
A vba sub to show some values and the datatype the variant assumes

Option Explicit

Private Function vValAndType(a As Variant) As String
    Dim vt As String
    Select Case varType(a)
        Case vbInteger: vt = "Integer"
        Case vbLong: vt = "Long"
        Case vbSingle: vt = "Single"
        Case vbDouble: vt = "Double"
        Case vbDate: vt = "Date"
        Case vbString: vt = "String"
    End Select
    
    vValAndType = a & ": " & varType(a) & "= " & vt
End Function
Sub varTypeDemo()
    Dim v As Variant, res As String
    
    v = "": res = vValAndType(v)
    v = "abc": res = res & vbLf & vValAndType(v)
    v = 11: res = res & vbLf & vValAndType(v)
    v = 165000: res = res & vbLf & vValAndType(v)
    v = #5/12/2016#: res = res & vbLf & vValAndType(v)
    v = 12.4: res = res & vbLf & vValAndType(v)
    v = 1.234E+100: res = res & vbLf & vValAndType(v)
    MsgBox res, , "vartype demo"
End Sub