Divide the sum of one cell over multiple cells until the value of the original cell is 0

M

mjacquot82

New Member
Joined
Jan 2, 2018
Messages
34
If anyone could help with this it would be greatly appreciated. I have entered the information in the "Complete @" column. I am trying to figure out a way that I can have it done automatically. Basically what I do is take the piece count, divide it by half hours so then I can figure out that those times what will be completed. I am fairly new an excel and feel that it doesn't seem too complicated but I can't figure it out. I have entered here in this example a lunch break but there are also other 15 minute breaks throughout the day.
Piece Count
Hours of Processing
Times
Complete @
100
.5
9:00 am
250
2
9:30 am
100
1000
3
10:00 am
62
10:30 am
62
11:00 am
62
11:30 am
62
11:59 am
166
LUNCH
12:30 pm
166
1:00 pm
166

<tbody>
</tbody>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
sorry, i do not have the skill to create a sheet function to make this work. it seems very simple sometimes, but you are really asking for a very dynamic model. i am interested in your problem, because i started working on something similar for the constraint work center at one of branches in the company i work for. my guess is there is not good sheet code for this problem. if you know a bit about VBA, perhaps you can re-purpose this code, it is currently not working correctly but may be a good starting point for you:

Code: 
Sub pupdate()
Application.ScreenUpdating = False


Sheet4.Select 'count of items in query
Dim a As Integer
a = Application.CountA(Sheet4.Range(Cells(2, 5), Cells(1000000, 5)))


Application.ScreenUpdating = True
'prompt for first item setup
UserForm2.Label1.Caption = "Please enter the planned start date and time for the current order:" & vbNewLine & Sheet4.Cells(2, 5).Value
UserForm2.Show
Sheet1.Cells(6, 6).Value = UserForm2.Label6.Caption
Sheet1.Cells(6, 7).Value = UserForm2.Label7.Caption
'UserForm1.Label1.Caption = "Was or is the Set-up of " & Sheet4.Cells(2, 5).Value & " performed outside of scheduled production time?"
'UserForm1.Show
Application.ScreenUpdating = False


'Dim var000
'var000 = Application.VLookup(Sheet3.Cells(2 + j, 1).Value, Sheet2.Range("E6:F1000000"), 2, False)
'Dim iserr000 As Boolean
'iserr000 = IsError(var000)
'If iserr000 = True Then
'Sheet3.Cells(2 + j, 3).Value = "???"
'Else
'Sheet3.Cells(2 + j, 3).Value = Application.VLookup(Sheet3.Cells(2 + j, 1).Value, Sheet2.Range("E6:F1000000"), 2, False)
'End If


For b = 1 To a 'loop for each line item in plan, get the basics from the query


Sheet1.Cells(5 + b, 2).Value = Sheet4.Cells(1 + b, 5).Value 'add the part number to the plan
Sheet1.Cells(5 + b, 3).Value = Sheet4.Cells(1 + b, 11).Value 'add star rating to the plan
Sheet1.Cells(5 + b, 4).Value = Sheet4.Cells(1 + b, 6).Value 'add the quantity to the plan
Sheet1.Cells(5 + b, 14).Value = Sheet4.Cells(1 + b, 12).Value 'check if setup is performed outside scheduled production time
    If IsEmpty(Sheet1.Cells(5 + b, 14).Value) = True Then
    Else
    Sheet1.Cells(5 + b, 13).Value = 0 'force override setup time to 0
    End If
Sheet1.Cells(5 + b, 5).Value = Sheet4.Cells(1 + b, 13).Value 'check if setup is performed outside scheduled production time
Dim var000 'add cycle time to the plan
    var000 = Application.VLookup(Sheet4.Cells(1 + b, 5).Value, Sheet5.Range("A2:C1000000"), 2, False) 'check if there is a cycle time in the table for this part.
    Dim iserr000 As Boolean
    iserr000 = IsError(var000)
    If iserr000 = True Then 'when there is an error, this should only occur when the part number is not in the list.
    Application.ScreenUpdating = True
    UserForm3.Label1.Caption = "We don't have a Cycle time or Setup time for this part,:" & vbNewLine & Sheet4.Cells(1 + b, 5).Value & vbNewLine & "What would you like me to put down?"
    UserForm3.Show
    Application.ScreenUpdating = False
    Sheet5.Select 'count of items in Cycle Time Table
    Dim ac As Integer
    ac = Application.CountA(Sheet5.Range(Cells(2, 1), Cells(1000000, 1)))
    Sheet5.Cells(2 + ac, 1).Value = Sheet1.Cells(5 + b, 2).Value 'add the part to the table
    Sheet5.Cells(2 + ac, 2).Value = UserForm3.Label3.Caption 'add the manually entered cycle time to the table
    Sheet1.Cells(5 + b, 10).Value = UserForm3.Label3.Caption 'add the cycle time to the plan
    Sheet5.Cells(2 + ac, 3).Value = UserForm3.Label5.Caption 'add the setup time to the table
    Else
    Sheet1.Cells(5 + b, 10).Value = Application.VLookup(Sheet4.Cells(1 + b, 5).Value, Sheet5.Range("A2:C1000000"), 2, False)
    End If
Sheet1.Cells(5 + b, 12).Value = Application.VLookup(Sheet4.Cells(1 + b, 5).Value, Sheet5.Range("A2:C1000000"), 3, False) 'add setup time to the plan.
Sheet1.Cells(5 + b, 18).Value = Sheet4.Cells(1 + b, 14).Value 'add the quantity to the plan


Next b


'we want all the basics already in the dashboard before these calculations. so perform a second loop to get the more difficult calculations and comparisons.
justcalcs


Sheet1.Select
Sheet1.Cells(1, 1).Select
Application.ScreenUpdating = True


End Sub
Sub justcalcs()


Application.ScreenUpdating = False


Sheet1.Select
Sheet1.Cells(1, 2).Value = 1
Dim d As Integer 'count of items in the dashboard
d = Application.CountA(Sheet1.Range(Cells(6, 2), Cells(1000000, 2)))


'Grab current time, day etc
Dim w
Dim x(1 To 6)
Dim y, z
w = Now
x(1) = Year(w)
x(2) = Month(w)
x(3) = Day(w)
x(4) = Hour(w)
x(5) = Minute(w)
x(6) = Second(w)
y = DateValue(w)
z = TimeValue(w)


If Sheet1.Cells(6, 6).Value = Sheet2.Cells(12, 1).Value Then
Else
Do Until Sheet1.Cells(6, 6).Value = Sheet2.Cells(12, 1).Value 'clear out old scheduled days
Sheet2.Select
Sheet2.Rows(3).EntireRow.Delete
Loop
End If




'loop to perform time calculations and logical decisions
For e = 1 To d


'total run time calculation
If Sheet1.Cells(5 + e, 14).Value = "Yes" Then 'if yes then disregard setup time info
    If IsEmpty(Sheet1.Cells(5 + e, 11).Value) Then 'if there is an override cycle time then use that
    Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 10).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 16).Value
    Else
    Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 11).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 16).Value
    End If
Else
    If IsEmpty(Sheet1.Cells(5 + e, 13).Value) Then 'if there is an override setup time, use that
        If IsEmpty(Sheet1.Cells(5 + e, 11).Value) Then 'if there is an override cycle time then use that
        Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 10).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 12).Value + Sheet1.Cells(5 + e, 16).Value
        Else
        Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 11).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 12).Value + Sheet1.Cells(5 + e, 16).Value
        End If
    Else 'otherwise just use the lookup setup time
        If IsEmpty(Sheet1.Cells(5 + e, 11).Value) Then 'if there is an override cycle time then use that
        Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 10).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 13).Value + Sheet1.Cells(5 + e, 16).Value
        Else
        Sheet1.Cells(5 + e, 17).Value = Round(((Sheet1.Cells(5 + e, 4).Value * Sheet1.Cells(5 + e, 11).Value) / 60) + 0.5, 0) + Sheet1.Cells(5 + e, 13).Value + Sheet1.Cells(5 + e, 16).Value
        End If
    End If
End If


'calculate end day and end time


Sheet1.Cells(4, 1).Value = TimeValue(TimeSerial(Int(Sheet1.Cells(5 + e, 17).Value / 60), Sheet1.Cells(5 + e, 17).Value - (Int(Sheet1.Cells(5 + e, 17).Value / 60) * 60), 0)) 'run time to be produced
If IsEmpty(Sheet1.Cells(5 + e, 15).Value) = False Then 'then there is a yes there, so we just make the end time say by end of shift and use the same day
Sheet1.Cells(5 + e, 8).Value = Sheet1.Cells(5 + e, 6).Value
Sheet1.Cells(5 + e, 9).Value = "Today"
Else


    'if (shift planned time - passed time) >= (time needed for order) then we can just fit it in the remaining shift.  so just use the start date as the end date.
    If (TimeValue(TimeSerial((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2)), (Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3)), 0)) _
    - (Sheet1.Cells(5 + e, 7).Value - Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value)) _
    > TimeValue(TimeSerial(Int(Sheet1.Cells(5 + e, 17).Value / 60), Sheet1.Cells(5 + e, 17).Value - (Int(Sheet1.Cells(6, 17).Value / 60) * 60), 0)) _
    Or (TimeValue(TimeSerial((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2)), (Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3)), 0)) _
    - (Sheet1.Cells(5 + e, 7).Value - Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value)) = TimeValue(TimeSerial(Int(Sheet1.Cells(5 + e, 17).Value / 60), Sheet1.Cells(5 + e, 17).Value - (Int(Sheet1.Cells(6, 17).Value / 60) * 60), 0)) Then
    Sheet1.Cells(5 + e, 9).Value = TimeValue(TimeSerial(Hour(Sheet1.Cells(5 + e, 7).Value), Minute(Sheet1.Cells(5 + e, 7).Value), 0)) _
    + TimeValue(TimeSerial(Int(Sheet1.Cells(7, 17).Value / 60), Sheet1.Cells(7, 17).Value - (Int(Sheet1.Cells(7, 17).Value / 60) * 60), 0)) ' calculate the end time.
    Sheet1.Cells(5 + e, 8).Value = Sheet1.Cells(5 + e, 6).Value 'copy the date from the start date
    Sheet1.Cells(6 + e, 6).Value = Sheet1.Cells(5 + e, 8).Value 'copy date to the next line item's start time
    Sheet1.Cells(6 + e, 7).Value = Sheet1.Cells(5 + e, 9).Value 'copy end time to next item start time
    'not adding a day to the day counter so that the next e will get the same start date
    Sheet1.Cells(4, 1).Value = -0.0001 'this forces the counter to be less than the threshold even if it is 0
    Else
        'if (shift planned time + threshold - passed time) >= (time needed for order) we will add the end time accurately but make the start day and time for the next order begin next shift as well as cause the setup to take place off shift
        If TimeValue(TimeSerial(Int(( _
        (Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
        + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
        + Int(Sheet1.Cells(3, 18).Value * 60)) / 60), ((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
        + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
        + Int(Sheet1.Cells(3, 18).Value * 60)) _
        - (Int(((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
        + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
        + Int(Sheet1.Cells(3, 18).Value * 60)) / 60) * 60), 0)) - _
        (Sheet1.Cells(5 + e, 7).Value - Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value) _
        > TimeValue(TimeSerial(Int(Sheet1.Cells(6, 17).Value / 60), Sheet1.Cells(6, 17).Value - (Int(Sheet1.Cells(6, 17).Value / 60) * 60), 0)) Or _
        TimeValue(TimeSerial(Int(( _
        (Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
        + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
        + Int(Sheet1.Cells(3, 18).Value * 60)) / 60), ((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
        + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
        + Int(Sheet1.Cells(3, 18).Value * 60)) _
        - (Int(((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
        + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
        + Int(Sheet1.Cells(3, 18).Value * 60)) / 60) * 60), 0)) - _
        (Sheet1.Cells(5 + e, 7).Value - Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value) _
        = TimeValue(TimeSerial(Int(Sheet1.Cells(6, 17).Value / 60), Sheet1.Cells(6, 17).Value - (Int(Sheet1.Cells(6, 17).Value / 60) * 60), 0)) Then
        Sheet1.Cells(5 + e, 9).Value = TimeValue(TimeSerial(Hour(Sheet1.Cells(5 + e, 7).Value), Minute(Sheet1.Cells(5 + e, 7).Value), 0)) _
        + TimeValue(TimeSerial(Int(Sheet1.Cells(5 + e, 17).Value / 60), Sheet1.Cells(5 + e, 17).Value - (Int(Sheet1.Cells(5 + e, 17).Value / 60) * 60), 0)) _
        + (Sheet1.Cells(5 + e, 7).Value - Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value) ' calculate the end time.
        Sheet1.Cells(5 + e, 8).Value = Sheet1.Cells(5 + e, 6).Value 'copy the date from the start date
            'check if next date has scheduled time, if not then goes to the first date with scheduled time (updates the days number)
            If IsEmpty(Sheet2.Cells(12 + Sheet1.Cells(1, 2).Value, 4).Value) = True Then
            Do Until IsEmpty(Sheet2.Cells(12 + Sheet1.Cells(1, 2).Value, 4).Value) = False
            Sheet1.Cells(1, 2).Value = Sheet1.Cells(1, 2).Value + 1
            Loop
            Else
            End If
            Sheet1.Cells(1, 2).Value = Sheet1.Cells(1, 2).Value + 1
        Sheet1.Cells(6 + e, 6).Value = DateValue(DateSerial(Year(Sheet2.Cells(12 + Sheet1.Cells(1, 2).Value, 1).Value), Month(Sheet2.Cells(12 + Sheet1.Cells(1, 2).Value, 1).Value), Day(Sheet2.Cells(12 + Sheet1.Cells(1, 2).Value, 1).Value))) 'get the start day off of the schedules page according to how many additional shifts forward we must go
        Sheet1.Cells(6 + e, 7).Value = Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 4).Value 'use shift start time for planned start time of the next line item
        Sheet1.Cells(1, 2).Value = Sheet1.Cells(1, 2).Value + 1 'add one to the day counter since we used up the entire shift
        Sheet1.Cells(4, 1).Value = -0.0001 'this forces the counter to be less than the threshold even if it is 0
        Else 'this is if the order must roll onto another shift
            'create Machine Time Counter for loop
            Sheet1.Cells(2, 2).Value = TimeValue(TimeSerial( _
            Int(( _
            (Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
            + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
            + Int(Sheet1.Cells(3, 18).Value * 60) _
            ) / 60), _
            ((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
            + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
            + Int(Sheet1.Cells(3, 18).Value * 60)) _
            - (Int(( _
            (Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 2).Value * 60) _
            + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 3).Value _
            + Int(Sheet1.Cells(3, 18).Value * 60) _
            ) / 60) * 60) _
            , 0))
            'create additional day counter
            Sheet1.Cells(3, 2).Value = 1
            Do Until Sheet1.Cells(4, 1).Value < Sheet1.Cells(2, 2).Value Or Sheet1.Cells(4, 1).Value = Sheet1.Cells(2, 2).Value
                'add the next shift and threshold to the Mach time holder
                Sheet1.Cells(2, 2).Value = Sheet1.Cells(2, 2).Value + _
                TimeValue(TimeSerial( _
                Int(( _
                (Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 2).Value * 60) _
                + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 3).Value _
                + Int(Sheet1.Cells(3, 18).Value * 60) _
                ) / 60), _
                ((Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 2).Value * 60) _
                + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 3).Value _
                + Int(Sheet1.Cells(3, 18).Value * 60)) _
                - (Int(( _
                (Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 2).Value * 60) _
                + Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 3).Value _
                + Int(Sheet1.Cells(3, 18).Value * 60) _
                ) / 60) * 60) _
                , 0))
                'add 1 to the additional day counter
                Sheet1.Cells(3, 2).Value = Sheet1.Cells(3, 2).Value + 1
            Loop
            'now actually calculate the end day and time
            Sheet1.Cells(5 + e, 8).Value = Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 1).Value 'the day is easy
            'add the additional day counter into the day counter
            Sheet1.Cells(1, 2).Value = Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value
            Sheet1.Cells(3, 2).Select ' clear out additional day counter
            Selection.ClearContents
            'calculate end time
            Sheet1.Cells(5 + e, 9).Value = (TimeValue(TimeSerial(Hour(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 5)), Minute(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value, 5)), 0))) _
            - ((Sheet1.Cells(2, 2).Value) - (Sheet1.Cells(4, 1).Value))
            'check if end time is during threshold, if it is then next item start date and time is beginning of next shift
                If ((Sheet1.Cells(2, 2).Value) - (Sheet1.Cells(4, 1).Value)) > TimeValue(TimeSerial(Int(Sheet1.Cells(3, 18).Value), ((Sheet1.Cells(3, 18).Value * 60) - (Int(Sheet1.Cells(3, 18).Value) * 60)), 0)) Then
                Sheet1.Cells(6 + e, 6).Value = Sheet1.Cells(5 + e, 8).Value 'just pull it off the last items end day
                Sheet1.Cells(6 + e, 7).Value = Sheet1.Cells(5 + e, 9).Value 'same critter, just get it from the last items end time
                Else
                    'because the last item ended during previous shift's threshold time, we need to check if this next item is supposed to run off shift, if it is then we want add it in the same day
                    If IsEmpty(Sheet1.Cells(6 + e, 14).Value) = True Then 'when yes we only want to perform the setup on the second shift so we simply change the run time to reflect and force the overide to 0
                    'because there is no yes here we want to assume that setup will take place on second shift so we change the total run time to reflect no setup, add a yes, and add a 0 for setup time
                    Sheet1.Cells(6 + e, 13).Value = 0 'no setup time Yay!
                    Sheet1.Cells(6 + e, 14).Value = "Yes"
                        If IsEmpty(Sheet1.Cells(5 + e, 11).Value) Then 'recalculate the total run time, if there is an override cycle time then use that
                        Sheet1.Cells(6 + e, 17).Value = Round(((Sheet1.Cells(6 + e, 4).Value * Sheet1.Cells(6 + e, 10).Value) / 60) + 0.5, 0) + Sheet1.Cells(6 + e, 16).Value
                        Else
                        Sheet1.Cells(6 + e, 17).Value = Round(((Sheet1.Cells(6 + e, 4).Value * Sheet1.Cells(6 + e, 11).Value) / 60) + 0.5, 0) + Sheet1.Cells(6 + e, 16).Value
                        End If
                    Else 'we were testing for a yes, there was one, so we know that the total run time is correct and does not need an update. but we stillneed to add the start time for the next item as  the next shifts start date and time
                    
                    Do Until IsEmpty(Sheet1.Cells(1, 3).Value) = False
                    'create additional day counter AGAIN
                    Sheet1.Cells(3, 2).Value = 1
                        If IsEmpty(Sheet2.Cells(11 + Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value, 4).Value) = True Then 'if there is not start time for the shift we know to go to the next one.
                        Sheet1.Cells(3, 2).Value = Sheet1.Cells(3, 2).Value + 1
                        Else 'always add one to the day counter when we have found a shift with time
                        Sheet1.Cells(1, 2).Value = Sheet1.Cells(1, 2).Value + Sheet1.Cells(3, 2).Value + 1
                        Sheet1.Cells(1, 3).Value = 1
                        End If
                    Loop
                    Sheet1.Cells(1, 3).Select
                    Selection.ClearContents
                    Sheet1.Cells(3, 2).Select
                    Selection.ClearContents
                    End If
                    'no need to check if it is supposed to run off shift, because that is the first thing we tested in this loop..  so just assume that it will get taken care of in the next e
                End If
        End If
    End If


Sheet1.Select
Sheet1.Cells(2, 2).Select
Selection.ClearContents
End If












Next e


Sheet1.Cells(3, 1).Value = Now()
Sheet1.Cells(4, 1).Select
Selection.ClearContents
Sheet1.Cells(1, 2).Select
Selection.ClearContents
Sheet1.Cells(3, 2).Select
Selection.ClearContents
Application.ScreenUpdating = True


End Sub
 
Upvote 0
I don't even know what the VBA abbreviation stands for lol what I just looked at is complete gibberish to me ! I think the problem is having cells with time values. I have had a couple of people at work look at the sheet I am currently working on. No one has any idea how to solve what I have posted here.
 
Upvote 0
the problem is building the logic for when an order stops and when the next order begins. Excel tracks seconds, minutes, hours, days etc with the time functions. for instance if you subtract 1/2/2018 from 1/3/2018 excel returns you a 1. you can also subtract 10:00 am from 11:00 am and get .041667. change the format to time and it gives you 1:00:00 AM. i guess what i am saying is you probably need to learn how the time functions work a little better first. take what you have learned and revamp your spreadsheet then bring more specific questions to the forum.

for example, the title of your post makes me want to say:

well what you are asking for can be done with a do until loop, but be very careful with that because you can potentially loop forever if your math and logic do not produce a 0 at some point.
 
Last edited:
Upvote 0
I hear you on the bring a more specific question to the forum. I honestly didn't know how to ask/post what I was looking to accomplish with excel.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Calculate and Identify Ordinary Hours, Double time or 1.5 Time
Replies
0
Views
71
ChetanPuri
C
V
  • Solved
Returning multiple value headers in one cell
Replies
5
Views
286
keda duck
K
esset
  • Solved
Splitting hours worked into multiple shift patterns
Replies
3
Views
546
esset
esset
HappyChappy
  • Solved
combining multiple cell Calculations
Replies
3
Views
132
Peter_SSs
Peter_SSs
M
  • Solved
Sumifs Formula not working
2
Replies
15
Views
813
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,798
Messages
6,121,630
Members
449,041
Latest member
Postman24

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