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.

DEFGUVAC
2MondayMondayTuesdayTuesdayMonTueMon-Tue
3Time InTime OutTime InTime OutShift
Length		Shift
Length		Time In
Between
Days
406001430070015308.08.016.5
517000130100014008.04.08.5
6143023000600-10001800-22008.0#value!#value!

<tbody>
</tbody>

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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Right now, I am entering this as shown in cells F6:G6 as this is the easiest for the staff to understand
Click to expand...

What is so easy about filling in two times in a column labelled 'Time in' or 'Time out'? I would call that cheating.

So I prefer a solution like the sheet below, where a extra row is added on which the extra shift(s) can be entered.
Blad1

DEFGUVAC
1MondayTuesdayShift lengthmon-tue
2Time inTime outTime inTime outmontueTime between shifts
3
4600143070015308,008,00
51700130100014008,004,008,50
61430230060010008,004,007,00
7 18002200 4,00

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"></colgroup><tbody>
</tbody>

Formeln der Tabelle
ZelleFormel
U4=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)))
V4=IF(F4="","",IF(MOD(DOLLARDE(G4/100,60)-DOLLARDE(F4/100,60),24)>5.4,MOD(DOLLARDE(G4/100,60)-DOLLARDE(F4/100,60),24)-0.5,MOD(DOLLARDE(G4/100,60)-DOLLARDE(F4/100,60),24)))
U5=IF(E5="","",IF(MOD(DOLLARDE(E5/100,60)-DOLLARDE(D5/100,60),24)>5.4,MOD(DOLLARDE(E5/100,60)-DOLLARDE(D5/100,60),24)-0.5,MOD(DOLLARDE(E5/100,60)-DOLLARDE(D5/100,60),24)))
V5=IF(F5="","",IF(MOD(DOLLARDE(G5/100,60)-DOLLARDE(F5/100,60),24)>5.4,MOD(DOLLARDE(G5/100,60)-DOLLARDE(F5/100,60),24)-0.5,MOD(DOLLARDE(G5/100,60)-DOLLARDE(F5/100,60),24)))
AC5=IF(OR(E5="",F5=""),"",IF(E5<F5,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)))
U6=IF(E6="","",IF(MOD(DOLLARDE(E6/100,60)-DOLLARDE(D6/100,60),24)>5.4,MOD(DOLLARDE(E6/100,60)-DOLLARDE(D6/100,60),24)-0.5,MOD(DOLLARDE(E6/100,60)-DOLLARDE(D6/100,60),24)))
V6=IF(F6="","",IF(MOD(DOLLARDE(G6/100,60)-DOLLARDE(F6/100,60),24)>5.4,MOD(DOLLARDE(G6/100,60)-DOLLARDE(F6/100,60),24)-0.5,MOD(DOLLARDE(G6/100,60)-DOLLARDE(F6/100,60),24)))
AC6=IF(OR(E6="",F6=""),"",IF(E6<F6,MOD(DOLLARDE(F6/100,60)-DOLLARDE(E6/100,60),24),MOD(DOLLARDE(F6/100,60)-DOLLARDE(E6/100,60),24)+IF(F6>=E6,24,0)))
U7=IF(E7="","",IF(MOD(DOLLARDE(E7/100,60)-DOLLARDE(D7/100,60),24)>5.4,MOD(DOLLARDE(E7/100,60)-DOLLARDE(D7/100,60),24)-0.5,MOD(DOLLARDE(E7/100,60)-DOLLARDE(D7/100,60),24)))
V7=IF(F7="","",IF(MOD(DOLLARDE(G7/100,60)-DOLLARDE(F7/100,60),24)>5.4,MOD(DOLLARDE(G7/100,60)-DOLLARDE(F7/100,60),24)-0.5,MOD(DOLLARDE(G7/100,60)-DOLLARDE(F7/100,60),24)))
AC7=IF(OR(E7="",F7=""),"",IF(E7<F7,MOD(DOLLARDE(F7/100,60)-DOLLARDE(E7/100,60),24),MOD(DOLLARDE(F7/100,60)-DOLLARDE(E7/100,60),24)+IF(F7>=E7,24,0)))

<tbody>
</tbody>

<tbody>
</tbody>


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Add a command button labelled 'extra shift'
Paste the following code in a module (and save your workbook as type .xlsm)
Code: 
Option Explicit

Sub extraShift()
'adds a row below the active cell to enable extra shifts

    Dim sht     As Worksheet
    Dim arow    As Long
    
    Set sht = ActiveSheet
    arow = ActiveCell.Row
    
    'insert a new row after the active row
    sht.Rows(arow + 1).Insert shift:=xlShiftDown
    
    'copy all but the shift columns to the new row
    sht.Range("A" & arow & ":C" & arow).Copy _
        Destination:=sht.Cells(arow + 1, "A")
    sht.Range("U" & arow & ":AZ" & arow).Copy _
        Destination:=sht.Cells(arow + 1, "U")
    
    'Goto cell below active cell
    ActiveCell.Offset(1, 0).Select
End Sub

assign the macro to the command button.
Now when you need extra shift(s), select the cell containing the first shift and click the command button and type the 2nd shift.
 
Upvote 0
bl1nd said:
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
Click to expand...
See here, particularly post #5 for details.
 
Upvote 0
ask2tsp said:
What is so easy about filling in two times in a column labelled 'Time in' or 'Time out'? I would call that cheating.

So I prefer a solution like the sheet below, where a extra row is added on which the extra shift(s) can be entered.
Blad1

Formeln der Tabelle
ZelleFormel
U4=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)))
V4=IF(F4="","",IF(MOD(DOLLARDE(G4/100,60)-DOLLARDE(F4/100,60),24)>5.4,MOD(DOLLARDE(G4/100,60)-DOLLARDE(F4/100,60),24)-0.5,MOD(DOLLARDE(G4/100,60)-DOLLARDE(F4/100,60),24)))
U5=IF(E5="","",IF(MOD(DOLLARDE(E5/100,60)-DOLLARDE(D5/100,60),24)>5.4,MOD(DOLLARDE(E5/100,60)-DOLLARDE(D5/100,60),24)-0.5,MOD(DOLLARDE(E5/100,60)-DOLLARDE(D5/100,60),24)))
V5=IF(F5="","",IF(MOD(DOLLARDE(G5/100,60)-DOLLARDE(F5/100,60),24)>5.4,MOD(DOLLARDE(G5/100,60)-DOLLARDE(F5/100,60),24)-0.5,MOD(DOLLARDE(G5/100,60)-DOLLARDE(F5/100,60),24)))
AC5=IF(OR(E5="",F5=""),"",<font color="#008000">IF(E5<f5,<font color="#0000ff">MOD(DOLLARDE(F5/100,60)-DOLLARDE(E5/100,60),24)</f5,,MOD(DOLLARDE(F5/100,60)-DOLLARDE(E5/100,60),24)+IF(F5>=E5,24,0)))
U6=IF(E6="","",IF(MOD(DOLLARDE(E6/100,60)-DOLLARDE(D6/100,60),24)>5.4,MOD(DOLLARDE(E6/100,60)-DOLLARDE(D6/100,60),24)-0.5,MOD(DOLLARDE(E6/100,60)-DOLLARDE(D6/100,60),24)))
V6=IF(F6="","",IF(MOD(DOLLARDE(G6/100,60)-DOLLARDE(F6/100,60),24)>5.4,MOD(DOLLARDE(G6/100,60)-DOLLARDE(F6/100,60),24)-0.5,MOD(DOLLARDE(G6/100,60)-DOLLARDE(F6/100,60),24)))
AC6=IF(OR(E6="",F6=""),"",<font color="#008000">IF(E6<f6,<font color="#0000ff">MOD(DOLLARDE(F6/100,60)-DOLLARDE(E6/100,60),24)</f6,,MOD(DOLLARDE(F6/100,60)-DOLLARDE(E6/100,60),24)+IF(F6>=E6,24,0)))
U7=IF(E7="","",IF(MOD(DOLLARDE(E7/100,60)-DOLLARDE(D7/100,60),24)>5.4,MOD(DOLLARDE(E7/100,60)-DOLLARDE(D7/100,60),24)-0.5,MOD(DOLLARDE(E7/100,60)-DOLLARDE(D7/100,60),24)))
V7=IF(F7="","",IF(MOD(DOLLARDE(G7/100,60)-DOLLARDE(F7/100,60),24)>5.4,MOD(DOLLARDE(G7/100,60)-DOLLARDE(F7/100,60),24)-0.5,MOD(DOLLARDE(G7/100,60)-DOLLARDE(F7/100,60),24)))
AC7=IF(OR(E7="",F7=""),"",<font color="#008000">IF(E7<f7,<font color="#0000ff">MOD(DOLLARDE(F7/100,60)-DOLLARDE(E7/100,60),24)</f7,,MOD(DOLLARDE(F7/100,60)-DOLLARDE(E7/100,60),24)+IF(F7>=E7,24,0)))

<tbody>
</tbody>

<tbody>
</tbody>


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Add a command button labelled 'extra shift'
Paste the following code in a module (and save your workbook as type .xlsm)
Code: 
Option Explicit

Sub extraShift()
'adds a row below the active cell to enable extra shifts

    Dim sht     As Worksheet
    Dim arow    As Long
    
    Set sht = ActiveSheet
    arow = ActiveCell.Row
    
    'insert a new row after the active row
    sht.Rows(arow + 1).Insert shift:=xlShiftDown
    
    'copy all but the shift columns to the new row
    sht.Range("A" & arow & ":C" & arow).Copy _
        Destination:=sht.Cells(arow + 1, "A")
    sht.Range("U" & arow & ":AZ" & arow).Copy _
        Destination:=sht.Cells(arow + 1, "U")
    
    'Goto cell below active cell
    ActiveCell.Offset(1, 0).Select
End Sub

assign the macro to the command button.
Now when you need extra shift(s), select the cell containing the first shift and click the command button and type the 2nd shift.
Click to expand...


On the physical schedule it is not labeled as In & Out so it is not confusing to them as that is how it has always been done... however, I am grateful for your post & am open to new solutions, however, I cannot get your macro to run, it doesn't show any errors, when I click it, nothing happens... :/




Peter_SSs said:
See here, particularly post #5 for details.
Click to expand...

Awesome, that explains it, thank you!
 
Upvote 0
Could you please show me a sample of the header rows plus about 10 rows of the physical schedule you mention?
The macro assumes you are on the physical schedule sheet in the cell after which you would like to add an extra shift. If that is not the case in your situation describe what is and I will try to adjust the macro.
 
Upvote 0
ask2tsp said:
Could you please show me a sample of the header rows plus about 10 rows of the physical schedule you mention?
The macro assumes you are on the physical schedule sheet in the cell after which you would like to add an extra shift. If that is not the case in your situation describe what is and I will try to adjust the macro.
Click to expand...


https://flic.kr/p/JMKVsy

Does that help?
 
Upvote 0
Yes that did help. I started to realize that inserting rows in that sheet would make it look ugly. So I explored the aaaa-bbbb concept.
If you try that using excel formulas only you get very complex, error prone, hard to maintain horror (the current formula's are on the edge).
I wrote a UDF called shiftLength that takes two arguments, the cell references to the time specs, and returns the total shift length as decimal hours.
See the following sheet
Excel Workbook
DEFGUVAC
1MondayTuesdayShift lengthmon-tue
2Time inTime outTime inTime outmontueTime between shifts
3
4600-9001400-2000180015308,5021,00
51700130100014008,004,008,50
61430230060010008,004,007,00
Blad1


The UDF goes like this
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
    
    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

This is a first draft that needs more work to improve error handling. Good enough for now to see if this is a way you would like to go.
 
Upvote 0
That looks like that would work! I don't have the file with me currently and wont be able to give it a try until tomorrow morning, before you go ahead and work on this any more, let me make sure it will work and get back to you!
Also, not shown in my screenshot, there is a second formula that calculates the time difference between the end of one shift on one day, and the beginning of the next shift the next day (sown in cell AC4:AC6 in my 1st post) that is needed so my conditional formatting can flag shifts that have less than 8.0 hours in between them. This does not need to apply to a split / double shift, only between the end of the last shift on one day and the beginning of the next days shift.

Would it be easier if I send you the file to take a look at?

I will post back tomorrow to let you know if what you have figured out so far will work, thanks for all your help so far! :))
 
Upvote 0
This does not need to apply to a split / double shift, only between the end of the last shift on one day and the beginning of the next days shift
Click to expand...
So this formula must be adapted too, because the end time and the begin time can both come from a hhmm field or a composite hhmm-hhmm field.

Other checks to consider:
  • is a number entered a valid time?
  • is a shift length > 12 ok?
  • for double shifts: the lunch break rule does not apply / per shift / per two shift
I will wait for your other findings. And don't worry, I like building unconventional solutions.
 
Upvote 0
ask2tsp said:
So this formula must be adapted too, because the end time and the begin time can both come from a hhmm field or a composite hhmm-hhmm field.

Other checks to consider:
  • is a number entered a valid time? I guess I could add this with a conditional format for any vales entered greater than 2400, but I am not that worried about it.
  • is a shift length > 12 ok? Yes, this is fine, although rarely occurs.
  • for double shifts: the lunch break rule does not apply / per shift / per two shift I would make it apply only once, so using the UDF you created seams to be working fine
I will wait for your other findings. And don't worry, I like building unconventional solutions.
Click to expand...


WOW! It works perfectly! :D Thank you!

One small tweak, right now it works great if there is data entered into the referenced cells, but if they are empty, it returns #value! instead of 0... is this possible?

For the other things you pointed out for me to consider, I have answered above, I don't think any of them will be an issue.

Also, do you see a way to adapt the formula/UDF for the difference between the end of of one day and start of the next day?
For clarification on this;

DEFGAC
2MonMonTueTueMon-Tue
3InOutInOutTime In
Between Days
4060014300800150017.5
514302300070012008.0
60600-12001800-233006301500#value!

<tbody>
</tbody>

Formula in AC4
<d4,mod(dollarde(f4 100,60)-dollarde(e4="" 100,60),24),mod(dollarde(f4="" 100,60),24)+if(f4="">=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)))

Formula in AC5
<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<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)))

Formula in AC6
<d6,mod(dollarde(f6 100,60)-dollarde(e6="" 100,60),24),mod(dollarde(f6="" 100,60),24)+if(f6="">=IF(OR(E6="",F6=""),"",IF(E6<D6,MOD(DOLLARDE(F6/100,60)-DOLLARDE(E6/100,60),24),MOD(DOLLARDE(F6/100,60)-DOLLARDE(E6/100,60),24)+IF(F6>=E6,24,0)))

Currently AC6 returns #value! as it cannot understand the entries in D6:E6, however, I would like it to return 7.0 as the result as that is the time between Mondays end time (2330) and Tuesdays start time (0630).

Other than that, I think the end is in sight!!</d6,mod(dollarde(f6></d5,mod(dollarde(f5></d4,mod(dollarde(f4>
 
Last edited:
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
368
Legacy 143009
L
HappyChappy
  • Question
Hours over basic
Replies
4
Views
172
Fluff
Fluff
T
  • Question
Time Sheet question
2
Replies
15
Views
508
tfowlerusa
T
E
  • Solved
Help with issues calculating using dates
2
Replies
16
Views
720
Anthony47
A
J
  • Question
Formula to SUM "On Hold" time overlaps (minus non-work time, weekends & public holidays)
Replies
5
Views
719
fjns
F

Forum statistics

Threads
1,214,947
Messages
6,122,413
Members
449,082
Latest member
tish101

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