Subtracting two numbers contained within one cell

B

bl1nd

New Member
Joined
Jul 17, 2016
Messages
32
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;

Code: 
=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;

Code: 
<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
 
Sort by date Sort by votes
For the calculation of the time between two times add the following code to the module:
Code: 
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))
    Else
        shift1End = num2time(--tEnd)
    End If

    If shift2Begin < shift1End Then shift2Begin = shift2Begin + 1
    timeBetween = (shift2Begin - shift1End) * 24
End Function

To return 0 if time not specified I added a few lines of code to the shiftLength function. Replace the shiftLength function with the following code:
Code: 
Function shiftLength(timeBegin As Range, timeEnd As Range) As Single
'calculates the shift length
'timeBegin and timeEnd are both a number (one shift or both number-number (2 shifts)

    Dim tBeg        As String
    Dim tEnd        As String
    Dim shift1Begin As Single
    Dim shift1End   As Single
    Dim shift2Begin As Single
    Dim shift2End   As Single
    Dim shift1Len   As Single
    Dim shift2Len   As Single
    Dim pos         As Integer
    
    tBeg = timeBegin.Text
    tEnd = timeEnd.Text
    
    If tBeg = "" Or tEnd = "" Then  'if not both specified
        shiftLength = 0             'return 0
        Exit Function
    End If
    
    pos = InStr(1, tBeg, "-")
    If pos > 0 Then
        shift1Begin = num2time(--Left(tBeg, pos - 1))
        shift1End = num2time(--Mid(tBeg, pos + 1))
        pos = InStr(1, tEnd, "-")
        shift2Begin = num2time(--Left(tEnd, pos - 1))
        shift2End = num2time(--Mid(tEnd, pos + 1))
    Else
        shift1Begin = num2time(--tBeg)
        shift1End = num2time(--tEnd)
        shift2Begin = 0
        shift2End = 0
    End If
    If shift1Begin > shift1End Then shift1End = shift1End + 1
    shift1Len = shift1End - shift1Begin
    If shift1Len > 5.4 / 24 Then shift1Len = shift1Len - 0.5 / 24
    
    If shift2Begin > shift2End Then shift2End = shift2End + 1
    shift2Len = shift2End - shift2Begin
    If shift2Len > 5.4 / 24 Then shift2Len = shift2Len - 0.5 / 24
    
    shiftLength = 24 * (shift1Len + shift2Len)
End Function

Replace current formula's that calculate time between shifts with timeBetween(cell with end time,cell with start time next shift) For example enter in AC4 =timeBetween(E4,F4)

One thing that crossed my mind: How do you calculate the time between ending sunday and beginning monday?

Good luck with this. I'm pretty confident you have a workable solution now.
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hey, sorry for the late response, I was away traveling for 2 days!

When using your modified shiftLength function above I now get an error that I didn't get before...
https://flic.kr/p/KRHCJM

Edit: I'm also getting the same error with the timeBetween module as well. What am I doing wrong?
 
Last edited:
Upvote 0
bl1nd said:
Hey, sorry for the late response, I was away traveling for 2 days!

When using your modified shiftLength function above I now get an error that I didn't get before...
https://flic.kr/p/KRHCJM

Edit: I'm also getting the same error with the timeBetween module as well. What am I doing wrong?
Click to expand...


Adding the following snipit from your new code for shiftLength to your original shiftLength module works;

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

It is just the new code for the timeBetween module that I am still having issues with..
 
Upvote 0
Sorry for the multi-post!

Got it sorted out, the replacement code for shiftLength was missing the first lines from your original shiftLength code;

Function num2time(hhmm As Integer) As Single
Dim hh As Integer
Dim mm As Integer

hh = CInt(hhmm / 100)
mm = hhmm - 100 * hh
num2time = TimeSerial(hh, mm, 0)
End Function
​


Adding that back in seams to solve the initial error that I was getting and now works great. It calculates the length of the shift for all shifts, including the double shifts.

However, as soon as I add the timeBetween code to the module and I input any new schedules, the debugger runs showing the following;
https://flic.kr/p/KRUEMT
 
Upvote 0
can you send me this test workbook so i can investigate what sub or function cannot be found?
 
Upvote 0
https://drive.google.com/open?id=0B0rbEKPk3ijrcTJBaERMNmw4LWc

Let me know if that works ok?

The last sheet, Aug-08, is using your shiftLength code, the previous two sheets are using my old formulas.
None of the sheets are currently using the timeBetween code yet.

The red, stripped conditional formatting on the schedule in sheet Aug-08 is a side effect to your code in shiftLength returning a 0 when the cells are blank. I tried changing the code to return "" when the cells are blank, but it didn't work.

As you can see in Aug-08, the double shifts located in L12:O12 calculate great, but as soon as I added the timeBetween code and tried typing or changing any shifts in the schedule, I would get an error pop-up in the debugger.

Thanks again!
 
Upvote 0
Not sure how this has happened but a statement in the added code in the shiftLength function was wrong causing a compile error. To avoid any further confusion throw away all the code in module2 and then paste this code into module2
Code: 
Function num2time(hhmm As Integer) As Single
    Dim hh As Integer
    Dim mm As Integer
    
    hh = CInt(hhmm / 100)
    mm = hhmm - 100 * hh
    num2time = TimeSerial(hh, mm, 0)
End Function

Function shiftLength(timeBegin As Range, timeEnd As Range) As Single
'calculates the shift length
'timeBegin and timeEnd are both a number (one shift or both number-number (2 shifts)

    Dim tBeg        As String
    Dim tEnd        As String
    Dim shift1Begin As Single
    Dim shift1End   As Single
    Dim shift2Begin As Single
    Dim shift2End   As Single
    Dim shift1Len   As Single
    Dim shift2Len   As Single
    Dim pos         As Integer
    
    tBeg = timeBegin.Text
    tEnd = timeEnd.Text
    
    If tBeg = "" Or tEnd = "" Then  'if not both specified
        shiftLength = 0             'return 0
        Exit Function
    End If
    
    pos = InStr(1, tBeg, "-")
    If pos > 0 Then
        shift1Begin = num2time(--Left(tBeg, pos - 1))
        shift1End = num2time(--Mid(tBeg, pos + 1))
        pos = InStr(1, tEnd, "-")
        shift2Begin = num2time(--Left(tEnd, pos - 1))
        shift2End = num2time(--Mid(tEnd, pos + 1))
    Else
        shift1Begin = num2time(--tBeg)
        shift1End = num2time(--tEnd)
        shift2Begin = 0
        shift2End = 0
    End If
    If shift1Begin > shift1End Then shift1End = shift1End + 1
    shift1Len = shift1End - shift1Begin
    If shift1Len > 5.4 / 24 Then shift1Len = shift1Len - 0.5 / 24
    
    If shift2Begin > shift2End Then shift2End = shift2End + 1
    shift2Len = shift2End - shift2Begin
    If shift2Len > 5.4 / 24 Then shift2Len = shift2Len - 0.5 / 24
    
    shiftLength = 24 * (shift1Len + shift2Len)
End Function

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))
    Else
        shift1End = num2time(--tEnd)
    End If

    If shift2Begin < shift1End Then shift2Begin = shift2Begin + 1
    timeBetween = (shift2Begin - shift1End) * 24
End Function
To make navigating the code a bit easier rename module2 to mUDF so you know that's the module holding the user defined functions.

A sample of aug-08 sheet using udf's
aug-08

*DEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
****
*******
*******
****

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:60px;"><col style="width:27.33px;"><col style="width:32.67px;"><col style="width:82.67px;"><col style="width:46px;"><col style="width:46px;"><col style="width:46px;"><col style="width:46px;"><col style="width:46px;"><col style="width:46px;"><col style="width:46px;"><col style="width:67.33px;"><col style="width:42px;"><col style="width:42px;"><col style="width:42px;"><col style="width:42px;"><col style="width:42px;"><col style="width:42px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="colspan: 2, align: center"]augustus 8[/TD]
[TD="colspan: 2, align: center"]augustus 9[/TD]
[TD="colspan: 2, align: center"]augustus 10[/TD]
[TD="colspan: 2, align: center"]augustus 11[/TD]
[TD="colspan: 2, align: center"]augustus 12[/TD]
[TD="colspan: 2, align: center"]augustus 13[/TD]
[TD="colspan: 2, align: center"]augustus 14[/TD]

[TD="colspan: 7, align: center"]Shift Length[/TD]

[TD="colspan: 6, align: center"]Time between shifts[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #c0c0c0, colspan: 14, align: center"]CAPTAINS[/TD]
[TD="colspan: 3, align: right"]Total Captain:[/TD]
[TD="align: center"]8,0[/TD]
[TD="align: center"]13,0[/TD]
[TD="align: center"]16,0[/TD]
[TD="align: center"]13,5[/TD]
[TD="align: center"]12,0[/TD]
[TD="align: center"]20,0[/TD]
[TD="align: center"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #ff0000"]*[/TD]

[TD="bgcolor: #ff0000"]*[/TD]

[TD="bgcolor: #ff0000, align: right"]0530[/TD]
[TD="align: left"]1400[/TD]
[TD="align: right"]0630[/TD]
[TD="align: left"]1500[/TD]
[TD="align: right"]0800[/TD]
[TD="align: left"]1630[/TD]
[TD="bgcolor: #ff0000"]*[/TD]

[TD="align: center"]#[/TD]

[TD="align: center"]24,0[/TD]
[TD="align: center"]*[/TD]
[TD="align: center"]*[/TD]
[TD="align: center"]*[/TD]
[TD="align: center"]8,0[/TD]
[TD="align: center"]8,0[/TD]
[TD="align: center"]8,0[/TD]
[TD="align: center"]*[/TD]

[TD="align: center"]*[/TD]
[TD="align: center"]*[/TD]
[TD="align: center"]*[/TD]
[TD="align: center"]16,5[/TD]
[TD="align: center"]17,0[/TD]
[TD="align: center"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]1430[/TD]
[TD="align: left"]2300[/TD]
[TD="align: right"]1330[/TD]
[TD="align: left"]1900[/TD]
[TD="align: right"]0700[/TD]
[TD="align: left"]1530[/TD]
[TD="align: right"]1400[/TD]
[TD="align: left"]2000[/TD]
[TD="bgcolor: #ff0000"]*[/TD]

[TD="bgcolor: #ff0000, align: right"]1800[/TD]
[TD="align: left"]0230[/TD]
[TD="bgcolor: #ff0000"]*[/TD]

[TD="align: center"]#[/TD]

[TD="align: center"]34,5[/TD]
[TD="align: center"]8,0[/TD]
[TD="align: center"]5,0[/TD]
[TD="align: center"]8,0[/TD]
[TD="align: center"]5,5[/TD]
[TD="align: center"]*[/TD]
[TD="align: center"]8,0[/TD]
[TD="align: center"]*[/TD]

[TD="align: center"]14,5[/TD]
[TD="align: center"]12,0[/TD]
[TD="align: center"]22,5[/TD]
[TD="align: center"]*[/TD]
[TD="align: center"]*[/TD]
[TD="align: center"]*[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
F2=D2+1
H2=F2+1
J2=H2+1
L2=J2+1
N2=L2+1
P2=N2+1
U3=SUM(U4:U7)
V3=SUM(V4:V7)
W3=SUM(W4:W7)
X3=SUM(X4:X7)
Y3=SUM(Y4:Y7)
Z3=SUM(Z4:Z7)
AA3=SUM(AA4:AA7)
AC3=IF(OR(E3="",F3=""),"",IF(E3<d3,mod(dollarde(f3 100,60)-dollarde(e3="" 100,60),24),mod(dollarde(f3="" 100,60),24)+if(f3="">=E3,24,0)))</d3,mod(dollarde(f3>
R4=MAX(U4:AA4)
S4=IF(R4>8.5,"O.T.","")
T4=SUM(U4:AA4)
U4=shiftLength(D4,E4)
V4=shiftLength(F4,G4)
W4=shiftLength(H4,I4)
X4=shiftLength(J4,K4)
Y4=shiftLength(L4,M4)
Z4=shiftLength(N4,O4)
AA4=shiftLength(P4,Q4)
AC4=timeBetween(E4,F4)
AD4=timeBetween(G4,H4)
AE4=timeBetween(I4,J4)
AF4=timeBetween(K4,L4)
AG4=timeBetween(M4,N4)
AH4=timeBetween(O4,P4)
R5=MAX(U5:AA5)
S5=IF(R5>8.5,"O.T.","")
T5=SUM(U5:AA5)
U5=shiftLength(D5,E5)
V5=shiftLength(F5,G5)
W5=shiftLength(H5,I5)
X5=shiftLength(J5,K5)
Y5=shiftLength(L5,M5)
Z5=shiftLength(N5,O5)
AA5=shiftLength(P5,Q5)
AC5=timeBetween(E5,F5)
AD5=timeBetween(G5,H5)
AE5=timeBetween(I5,J5)
AF5=timeBetween(K5,L5)
AG5=timeBetween(M5,N5)
AH5=timeBetween(O5,P5)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
The red, stripped conditional formatting on the schedule in sheet Aug-08 is a side effect to your code in shiftLength returning a 0 when the cells are blank. I tried changing the code to return "" when the cells are blank, but it didn't work.
Click to expand...
That's because the function's return type is Single (numeric, floating point)

Change the format conditions (just one example)

from
=$U9<4

to
=AND($U9<4,$U9>0)
 
Upvote 0
Awesome!! Thank you so much!

One last possible tweak, for the timeBetween code, it is not taking into account the difference in days like the old formula did.

EG:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"]Mon
In[/TD]
[TD="align: center"]Mon
Out[/TD]
[TD="align: center"]Tue
In[/TD]
[TD="align: center"]Tue
Out[/TD]
[TD="align: center"]Time Between
Mon-Tue[/TD]
[/TR]
[TR]
[TD="align: center"]0730[/TD]
[TD="align: center"]1130[/TD]
[TD="align: center"]1430[/TD]
[TD="align: center"]2300[/TD]
[TD="align: center"]3.0[/TD]
[/TR]
</tbody>[/TABLE]

Using the old formula it would of given me 27.0 which is the correct time difference between the two days.

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

Is there a way to add this to the code?

Other than that, I think it is finally there! :D
 
Upvote 0
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
Code: 
    If shift2Begin < shift1End Then shift2Begin = shift2Begin + 1
to
Code: 
    shift2Begin = shift2Begin + 1
(remove the condition)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Formula for IF to calculate and add and subtract production remaining
Replies
3
Views
550
Legacy 143009
L
HappyChappy
  • Question Question
Hours over basic
Replies
4
Views
393
Fluff
Fluff
Z
  • Question Question
Trying to use a Macro to fix timesheet numbers for regular time and overtime.
2
Replies
13
Views
473
myall_blues
myall_blues
T
  • Question Question
Time Sheet question
2
Replies
15
Views
904
tfowlerusa
T
E
  • Solved
Help with issues calculating using dates
2
Replies
16
Views
1K
Anthony47
A

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

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