Solved

Populate an End Date in Excel

Posted on 2014-03-15
56
422 Views
Last Modified: 2014-03-19
I'm trying to schedule classes that have a set amount of class hours. I'd like excel to do the math and populate an end date for me.

If I did the following:

- Enter a start time (6 pm)
- Enter a end time (9pm)
- Enter days per week using excel serial numbes (1 for Monday and 3 for Wed )
- Enter a start date (3/17/14)
- Enter hours
Can excel populate the end date using above values?

I'd like to include a holiday table. If the start and end date ranges fall on a date in the holiday table, 3/26/14, can I get an option to either populate the next available end date in the end date cell or back up the start date (show the what the start will be so I can change it).

The option can be a pop up for yes to populate the next available end date or cancel so I can enter the start date shown (or even change the start date?).  


I've included a sample spreadsheet. Thanks!
End-Date.xlsx
0
Comment
Question by:tracyms
  • 24
  • 22
  • 9
  • +1
56 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39931494
Question
Does this means:
that you want to affect 3 hours on Monday and 3 hours on wed for a total of 40 hours starting 3/17/14 and need to find at what date these 40 hours will fall on taking into consideration holidays if any ?
gowflow
0
 

Author Comment

by:tracyms
ID: 39931515
Yes. When you say "fall" on I'm assuming "end" on and that's what I need.

"Does this means:
that you want to affect 3 hours on Monday and 3 hours on wed for a total of 40 hours starting 3/17/14 and need to find at what date these 40 hours will fall (END) on taking into consideration holidays if any ? "
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39931516
if my previous assumption is correct then put this formula in L2

=IF(J2+ROUNDUP((K2/(HOUR(B2)-HOUR(A2)))/COUNTA(C2:I2),0)*7=P2,P2+1,J2+ROUNDUP((K2/(HOUR(B2)-HOUR(A2)))/COUNTA(C2:I2),0)*7)

and if it falls on the same date as the holiday it displays the next day else it give you the day that it should fall on.

chk the file

gowflow
End-Date.xlsx
0
 

Author Comment

by:tracyms
ID: 39931551
It looks like the formula is not starting the hours on the start date. I entered 6 hours in the hours cell. The start date is 3/17/14 and when I enter 6 hours the end date should be 3/19/14. So 3 hours on the start date (Monday) and 3 hours on end date (Wed.) are 6 hours...thanks.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39931600
hold on for the hours. keep the hours as is as it is already tricky to ge the correct number of days.

My previous formula has been revised to get the correct date check this one.

=IF(J2+ROUNDUP((K2/(HOUR(B2)-HOUR(A2))/COUNTA(C2:I2))*7-(7-WEEKDAY(J2)),0)=P2,P2+1,J2+ROUNDUP((K2/(HOUR(B2)-HOUR(A2))/COUNTA(C2:I2))*7-(7-WEEKDAY(J2)),0))

look at the file. I made the calculations manually.
Is this as per your calculations ? if yes then I will look at the hours.
gowflow
End-Date.xlsx
0
 

Author Comment

by:tracyms
ID: 39931691
It's 36 hours when I counted from your start date (skipping the holiday of 3/26/14 - meaning I didn't count that day).

You had 3/14/14 as the start date and I started my counting the following Monday on 3/17/14. I counted 3 hours starting on 3/17/14 for every Mon and Wed, skipped the holiday on 3/26/14 (Wed). I had 36 hours as of 4/28/14. Your end date was 4/29/14.
0
 

Author Comment

by:tracyms
ID: 39931708
Fyi - it's 39 hours if I make the end date 4/30/14 (Wed). So it leaves 1 hour - not sure how to handle the left over or additional hours. Maybe if I make a another cell to hold those hours I can adjust my start and end times...?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39931773
skipped the holiday on 3/26/14 (Wed).  !!!!! ??? Well you had Mon as 1 and wed as 3 !!! this means you are adopting the Mon to sun week which makes the date 3/26/2014 give a weekday of 4 and not 3 !!!!

If you want that to be a wed which it is then you need to adopt the Sunday Saturday week where Sunday =1 Monday =2 Tuesday = 3 Wednesday = 4 Thursday = 5 Saturday = 6

Do we agree on this ?

basis your answer then I will modify the formula as in my mind the holiday was meant differently.

that is the trick !!! I am testing if the date fall on the holiday then take the next day where as you meant to say if the holiday is a day that is counted then skip !!!!

gowflow
0
 

Author Comment

by:tracyms
ID: 39931827
I'm sorry about messing up the serial day numbers - :-( - yes we are in agreement. Thank you!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39931849
Ok one last question, looking at it very closer and with the hours also in variable and the holiday and days of the weeks etc... this is becoming widely challenging to get it as a formula (as far as I am concerned !!) formulas not being my cup of tea ! :)

would you agree for a VBA solution ?

and if VBA would be acceptable then do you foresee I guess more rows and more holidays ??? and how would you have say several items ?

As if I need to design something I need to have the full picture.

gowflow
0
 

Author Comment

by:tracyms
ID: 39931875
A VBA solution is just fine.  Can you use can use the spreadsheet as a guide as each row will be one that column? Could the holiday table be a named range for getting the holidays? Thanks.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39931884
Well I need a sample with several rows !!! pls this is not an easy task so take sometime and give me a sample that is more like what you want.

and when the holidays are several ones shall we look each and every time we analyze a row for all holidays right ?

The idea for you to provide a sample is that at the same time you give me the result. Like when you put a row you put me a cell say in Col M that you call Expected result and you put the date there.

So I can make my logic and test and at least have the answers too !! :)

If you want me to facilitate your life, you need to facilitate mine as well at least in the search for the correct result. As so far I am spending my time calculating manually what the result should be ! :(
gowflow
0
 

Author Comment

by:tracyms
ID: 39932063
Nothings easy huh? :-) Let me think about this, ideally a formula to drag down would be better. Thanks.
0
 

Author Comment

by:tracyms
ID: 39932091
What if I didn't want the holidays? Would that work better as a formula?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39932256
Why are you afraid of VBA ?

VBA is stable Formulas could be messed up easily and when it is a complicated one you need a knowlegable to be able to fix it.

I made up a sample and working on it.

I bet you will be more than safissfied !! I don't believe you give up so easily !
gowlfow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39932274
Question:

What do you want to happen if the Start date say 3/14/2014 which is a Fri coincide with a work Day (like you had put a value under Fri) shall we count that first day as a workday or we start counting days the following workday ?


gowflow
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39932480
If you have Excel 2010 or a later version you can use WORKDAY.INTL function here for a [relatively] simple formula solution. That function returns a future date given a start date and number of workdays to add - you can customise the workdays (using the contents of C2:I2) and you can exclude holidays.

I changed A2 and B2 so they are valid times and then used this formula in L2 for the future date

=WORKDAY.INTL(J2,CEILING(K2/((B2-A2)*24),1),((C2="")+0)&((D2="")+0)&((E2="")+0)&((F2="")+0)&((G2="")+0)&((H2="")+0)&((I2="")+0),P2)

See attached

If you want to simplify that a little you can delete C2:I2, format C2:I2 as text and use a 0 for working day and a 1 for non working day then formula simplifies to this

=WORKDAY.INTL(J2,CEILING(K2/((B2-A2)*24),1),C2&D2&E2&F2&G2&H2&I2,P2)

see attached with both options (second one on sheet 2)

regards, barry
End-Date-barry.xlsx
0
 

Author Comment

by:tracyms
ID: 39932575
gowflow,

To answer your question, any may can be used as a start or end date and I always want to include/count those dates as part of the hours.

I use VBA extensively (not an expert) and love what it can do. I've used it to make many Excel applications - most of which I've gotten help from this very forum to create! I am still interested in your VBA method.

This particular issue I'm getting help on will be a part of a larger project that has a LOT of vba code and I didn't want to include more if I could get a basic formula instead.  

I've attached a screen shot of my larger project that is working fine but I wanted to take the guess work out of adding the end date to have the total course hours match the course master hours.

I didn't want to complicate what I needed that's why I asked for help on only what I was trying to accomplish...I hope it makes better sense now.

Barry,
I have Excel 2007 but I can test this on 2010 as I have it work. I'll check it out and let you know.

Thanks guys!
Schedules.pdf
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39932606
Hello tracyms.

Given your comments about start dates I realise that my suggested formulas need a small tweak, so that J2 becomes J2-1, e.g. first formula (sheet1) should be:

=WORKDAY.INTL(J2-1,CEILING(K2/((B2-A2)*24),1),((C2="")+0)&((D2="")+0)&((E2="")+0)&((F2="")+0)&((G2="")+0)&((H2="")+0)&((I2="")+0),P2)

...second formula should be this in sheet 2

=WORKDAY.INTL(J2-1,CEILING(K2/((B2-A2)*24),1),C2&D2&E2&F2&G2&H2&I2,P2)

Those formulas won't work in Excel 2007 but I added sheet3 with a formula that will work in that version, i.e. this "array formula"

=J2+SMALL(IF(ISNUMBER(MATCH(WEEKDAY(J2+ROW(INDIRECT("1:"&K2*10))-1,2),C2:I2,0)),IF(COUNTIF(P2,J2+ROW(INDIRECT("1:"&K2*10))-1)=0,ROW(INDIRECT("1:"&K2*10))-1)),CEILING(K2/((B2-A2)*24),1))

confirmed with CTRL+SHIFT+ENTER

That 3rd version needs the day numbers, 1-7 in C2:I2 for it to work

See revised attachment

regards, barry
End-Date-barry2.xlsx
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39932788
Smart function Barry!
Only one disadvantage, can only handle one holiday.
Here is a VBA function, where there can be a list of holidays.
The lesson day marking can be anything, number or text, as the function just looks if there is something in the cell.
Principle is to loop from start day forward, checking if it is a lesson day, and not a holiday.
Then add hours per day until total hours is reached.

Function EndDate(rgStartTime As Range, rgEndTime As Range, rgLessonDays As Range, rgStartDate As Range, rgHoursTotal As Range, rgHolidays As Range) As Date
    Application.Volatile
    Dim HoursPerDay As Double, HoursUsed As Double, UseDay As Integer
    Dim i As Integer
    
    HoursPerDay = Hour(rgEndTime.Value - rgStartTime.Value)
    EndDate = rgStartDate.Value
    HoursUsed = 0
    Do
        UseDay = 0
        For i = 1 To rgLessonDays.Columns.Count
            If Weekday(EndDate, vbMonday) = i And Len(rgLessonDays.Cells(1, i)) > 0 Then
                UseDay = 1
            End If
        Next i
        For i = 1 To rgHolidays.Rows.Count
            If EndDate = rgHolidays.Cells(i, 1) Then
                UseDay = 0
            End If
        Next i
        If UseDay = 1 Then
            HoursUsed = HoursUsed + HoursPerDay
        End If
        EndDate = EndDate + 1
    Loop Until HoursUsed >= rgHoursTotal.Value
    EndDate = EndDate - 1
End Function

Open in new window

End-Date-barry2-with-VBA-functio.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39932814
Well this is my results for your request.

PLs chk the file and I put some crazy examples with a list of holidays, and to follow when you activate the button End Date it will put the results in Col L but also put a trace of all the calculations in sheet Trace. You can check there to see if all is ok.

here is the code for that

Sub FindEndDate()
Dim WS As Worksheet
Dim WSTrace As Worksheet
Dim MaxRow As Long, MaxRowTrace As Long, I As Long, J As Long, K As Long, L As Long, M As Long
Dim cHolidays As Long
Dim StDate As Date, EndDate As Date
Dim sTime As Variant
Dim vTime As Double, TotTime As Double
Dim TotDays As Long, WorkDays As Long, NextWorkDay As Long, lHolidays As Long, lExtraHolidays As Long
Dim cCell As Range
Dim sWeekdayWorkday As String, sWeekdayStartDay As String
Dim LDays As Integer
Dim bHoliday As Boolean

'---> Disbale Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WS = ActiveSheet
Set WSTrace = Sheets("Trace")
MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
MaxRowTrace = 2

'---> Clean Previous End Date Column
WS.Range("L2:L" & WS.Rows.Count).ClearContents
WSTrace.Range("2:" & WSTrace.Rows.Count).EntireRow.Delete

'---> Start Process
For I = 2 To MaxRow
    '---> Update Trace with Start Info
    WSTrace.Cells(MaxRowTrace, "A") = WS.Cells(I, "J")
    WSTrace.Cells(MaxRowTrace, "B") = WS.Cells(I, "K")
    WS.Range("C" & I & ":I" & I).Copy
    WSTrace.Range("E" & MaxRowTrace).PasteSpecial xlPasteValues
    
    '---> Determin Hours/Minutes Needed
    sTime = Format(WS.Cells(I, "B") - WS.Cells(I, "A"), "hh:mm:ss")
    vTime = Hour(sTime) + Minute(sTime) / 60 + Second(sTime) / 3600
    TotTime = WS.Cells(I, "K")
    RemainTime = TotTime
    StDate = WS.Cells(I, "J")
    sWeekdayStartDay = Format(WS.Application.WorksheetFunction.Weekday(WS.Range("J" & I), 11), "@")
    sWeekdayWorkday = ""
    EndDate = StDate
    WorkDays = WS.Application.WorksheetFunction.CountA(WS.Range("C" & I & ":" & "I" & I))
    
    '---> Get the Weekdays of the workdays
    For J = 3 To 9
        If WS.Cells(I, J) <> "" Then
            If sWeekdayWorkday <> "" Then sWeekdayWorkday = sWeekdayWorkday & ","
            sWeekdayWorkday = sWeekdayWorkday & Format(WS.Cells(I, J).Column - 2, "@")
        End If
    Next J
       
    
    '---> First Pass Calculate End Date without Holiday Impact
    '---> Get Total Days
    'ROUNDUP((K2/(HOUR(B2)-HOUR(A2))/COUNTA(C2:I2))*7-(7-WEEKDAY(J2)),0)
    TotDays = Round((TotTime / vTime), 0)
    
    '---> Update Trace
    WSTrace.Cells(MaxRowTrace, "C") = vTime
    WSTrace.Cells(MaxRowTrace, "D") = TotDays
    MaxRowTrace = MaxRowTrace + 1
    WSTrace.Cells(MaxRowTrace, Application.WorksheetFunction.Weekday(StDate, 11) + 4) = StDate
    MaxRowTrace = MaxRowTrace + 1
    lHolidays = 1
    lExtraHolidays = 0
    bHoliday = False
    
    Do
        If bHoliday Then bHoliday = False
        For K = lHolidays To TotDays
            '---> Determine Start Day is a workday
            If K = 1 Then
                If InStr(1, sWeekdayWorkday, sWeekdayStartDay, vbBinaryCompare) <> 0 Then
                    '---> Start Day falls in a workday so count 1 day as of this start day
                    EndDate = StDate
                    MaxRowTrace = MaxRowTrace + 1
                End If
            Else
                sWeekdayStartDay = Format(Application.WorksheetFunction.Weekday(EndDate, 11), "@")
                
            End If
                    
            '---> Find Next Work Day
            L = Val(sWeekdayStartDay)
            NextWorkDay = 0
            LDays = 0
            Do
                If L = 7 Then
                    L = 1
                Else
                    L = L + 1
                End If
                LDays = LDays + 1
                
                If InStr(1, sWeekdayWorkday, Format(L, "@"), vbBinaryCompare) <> 0 Then
                    NextWorkDay = L
                End If
                    
            Loop Until NextWorkDay <> 0
                            
            '---> Get the End Date
            EndDate = EndDate + LDays
            sWeekdayStartDay = Format(NextWorkDay, "@")
            
            '---> Update Trace
            WSTrace.Cells(MaxRowTrace, L + 4) = EndDate
            
            '---> Find if EndDate Falls on a Holiday then Increase TotDays by 1
            Set cCell = WS.Range("Holidays").Find(what:=EndDate, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
            If Not cCell Is Nothing Then
                bHoliday = True
                lExtraHolidays = lExtraHolidays + 1
                WSTrace.Cells(MaxRowTrace, L + 4) = "Holiday"
                DoEvents
                '---> Update Trace
                WSTrace.Cells(MaxRowTrace, "L") = cCell
                WSTrace.Cells(MaxRowTrace, "M") = Format(cCell, "Ddd")
            End If
            
            '---> Update Trace
            MaxRowTrace = MaxRowTrace + 1
            
        Next K
        
        If bHoliday Then
            lHolidays = TotDays + 1
            TotDays = TotDays + lExtraHolidays
            lExtraHolidays = 0
        End If
        
    Loop Until bHoliday = False
    
    '---> Affect EndDate to Col L
    WS.Cells(I, "L") = EndDate
    
Next I


'---> Enbale Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With


'---> Advise User
MsgBox ("End Date updated successfully.")

End Sub

Open in new window



Regards
gowflow
End-Date.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39932821
Forgot to mention that you just need to mark x in the corresponding day that you want to allow work no need for a number or a M T W ... just an x.

I broke the hours into half you can also go minutes or anything you like will also do.

gowflow
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39932848
Hello gowflow,

I only used one holiday in my examples because that matched the original sheet........ but all of the formulas I suggested can handle a range of holidays, so in place of P2 you can use any range, e.g. P$2:P$100 or a named range like HolidayList

regards, barry
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39932862
Barry

hgholt made the comment to you about holiday not me !
gowflow
0
 

Author Comment

by:tracyms
ID: 39932866
Barry,

As I'm looking at your formula I'm thinking how I would incorporate into my project - some things I need to consider:

- I may not need the holidays since I have a holiday table in my project (your formula looks to be using the adjacent row for the holiday and not the entire holiday column - there may be more than one holiday between the start and end dates)

- I use serial numbers to identify the days of the week as gowflow pointed out above because they are used in my project which I why I noted serial numbers in my original post:

Sunday =1 Monday =2 Tuesday = 3 Wednesday = 4 Thursday = 5 Saturday = 6

Your formula is counting 1-7 starting on Monday in sequential order. It does, however look to be working as stated.

Also, the start date will always be the first date of the class and the hours will be counted beginning at the start date through the end date - the end date will also be counted since that is the last date the class is held/attended.

Your formula may work if the serial number is used...I will have to test.
0
 

Author Comment

by:tracyms
ID: 39932870
Wow - many posts since I wrote the above, I'm reading through them now.
0
 

Author Comment

by:tracyms
ID: 39932900
gowflow,

I get an error when clicking end date that says

Unable to get weekday property of worksheetfunction class - debug sends me here:

  sWeekdayStartDay = Format(WS.Application.WorksheetFunction.Weekday(WS.Range("J" & I), 11), "@")
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39932909
what version of Excel you have?
gowflow
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:tracyms
ID: 39932917
Excel 2007
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39932927
thought so.

Yoou should replace the 11 by 2 in the formula of Weekday as this is supported in 2010+ (the 11)

it is fixed in this version for 2007.
gowflow
End-Date2007.xlsm
0
 

Author Comment

by:tracyms
ID: 39932945
gowflow - HOLLY COW! I am logged in to my work pc (which has Excel 201) - your spreadsheet is impressive to say the least! I am evaluating it right now but can it also work in Excel 2007? Way cool... :-)
0
 

Author Comment

by:tracyms
ID: 39932947
Great, thanks for posting 2007 version - can I use the 2007 version in 2010 - I know I can do the other way around.
0
 

Author Comment

by:tracyms
ID: 39932955
Never mind, the 2007 version works in 2010. :-) Give me a minute to process what I have - thanks guys.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39932965
Hello tracyms,

as per one of my comments above, all my suggested formulas will work with a range of holidays rather than a single cell.

If you are interested in the formula version it can be tweaked to work a number of different ways depending on how you want to designate working days. The very first formula I suggested doesn't care what you put in the cells - it can be anything as long as the cell is populated.

Let me know if you have any problems adjusting those, and I can suggest any changes you need.

Hello gowflow,

Apologies, I wrongly attributed that comment to you

regards, barry
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39932976
Barry don't worry, no sweat, no problem.
gowflow
0
 

Author Comment

by:tracyms
ID: 39933023
Barry,

I'd like to try your formula without the holidays - the one you said that only checks if the Mon-Sun range is empty...I want to put your formula in one of my end date cells in my project spreadsheet. Every time I tried to remove the holiday cell references in your formula excel throws an error.

Can you repost the formula (2007 version) without factoring in holidays?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39933196
Hello tracyms,

The only formula that works in Excel 2007 is the array formula (in sheet3 in my 2nd attachment). If you take out the holiday part (and adjust the days so that it treats 1 as Sunday up to 7 = Saturday) then it's like this:

=J2+SMALL(IF(ISNUMBER(MATCH(WEEKDAY(J2+ROW(INDIRECT("1:"&K2*10))-1),C2:I2,0)),ROW(INDIRECT("1:"&K2*10))-1),CEILING(K2/((B2-A2)*24),1))

confirmed with CTRL+SHIFT+ENTER

The correct numbers need to be filled in C2:I2 with that version, although I included another version in sheet2 where the cells simply need to be populated (with anything)

That formula is this:

=J2+SMALL(IF(ISNUMBER(MATCH(WEEKDAY(J2+ROW(INDIRECT("1:"&K2*10))-1),IF(C2:I2<>"",{1,2,3,4,5,6,7}),0)),ROW(INDIRECT("1:"&K2*10))-1),CEILING(K2/((B2-A2)*24),1))

See attached examples

regards, barry
End-Date-barry3.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39933645
Barry

I tried your formula in the wb I posted (removed the holidays deleted the Range Holidays) and got a difference of 1 day between your and mine.

Mine                Yours
3/19/2014      3/18/2014
3/18/2014      3/17/2014
4/2/2014              4/1/2014
5/16/2014      5/15/2014
7/3/2014              7/2/2014
3/28/2014      3/27/2014

and if you look at the days like 3/18/2014 is a Tue where the example shows work on Mon and Wed
Can you clarify why this difference ?

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39933649
@Tracyms

I just noticed that in VBA the Round function does not give the correct results as we want a Roundup instead as when for example we have 42 hours worked in 5hours interval this gives 8.4 the round VBA function returns 8 Days where in fact we want 9 days.

So please replace the following line in my Sub
TotDays = Round((TotTime / vTime), 0)

by this line
TotDays = Application.WorksheetFunction.RoundUp((TotTime / vTime), 0)

gowflow
0
 

Author Comment

by:tracyms
ID: 39934790
Thanks gowflow, I made the change - I am still working on different ways to add this into my project...I really like what you've done. I'm looking into Barry's as well since it's a quick solution and I can use now - if it works - until I can get your solution incorporated in my project.

Barry, I'm getting a #Num error on your formula and trying to work through that - I put your excel 2007 formula in excel 2010 because excel 2010 lets you step through the formula to find where the error occurs - when I evaluate, at the end I get "4175+#NUM".

I also changed the range you had here - IF(C2:I2<>"",{1,2,3,4,5,6,7}),0)) to my range

IF(O10&P10&Q10&R10&S10&T10&U10<>"",{1,2,3,4,5,6,7}),0)) since I was getting an error with that before I change it.

I used the following formula that replaces your ranges with mine:

=E10+SMALL(IF(ISNUMBER(MATCH(WEEKDAY(E10+ROW(INDIRECT("1:"&AC10*10))-1),IF(O10&P10&Q10&R10&S10&T10&U10<>"",{1,2,3,4,5,6,7}),0)),ROW(INDIRECT("1:"&AC10*10))-1),CEILING(AC10/((BT10-BS10)*24),1))

I'm just not getting past the #NUM error.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39934965
ok fine.

I see you have different ranges and columns from the one you posted. So to make your integration easier if and when you choose VBA to incorporate, then just give me the equivalent in Columns from the one you posted.

Like
Posted Col A, B, C, D etc...
Now     Col D, F, G, H etc...

and will make sure I modify the code to suits your production workbook as sometimes with references it is not that obvious to try and change it.

gowflow
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39935142
Hello gowflow,

In that last formula I posted, i.e.

=J2+SMALL(IF(ISNUMBER(MATCH(WEEKDAY(J2+ROW(INDIRECT("1:"&K2*10))-1),IF(C2:I2<>"",{1,2,3,4,5,6,7}),0)),ROW(INDIRECT("1:"&K2*10))-1),CEILING(K2/((B2-A2)*24),1))

I switched the weekdays in line with earlier comments, so because we now have Sun=1 through to Sat = 7 I assumed that the days would be shown starting with a Sunday too, so with that formula I'm assuming that if C2 is populated that designates a Sunday, D2 for Monday etc. hence the discrepancy in results

regards barry
0
 

Author Comment

by:tracyms
ID: 39935231
gowflow,

I've brought your spreadsheet into my project spreadsheet - I haven't moved anything around as I'm simply having your columns point to my columns - this way I don't have to move anything around.

So - what happens is every time I enter information in my project spreadsheet it will send it to your spreadsheet in same column and row. Then I only need to click your button to add all the end dates (which I set the end dates in my spreadsheet to equal your end dates).

This is good BUT I'm getting an  type mismatch error - TotTime = WS.Cells(I, "K") - I've checked that I have all the columns formatted as you have yours and I'm still checking on the issue - any light you can shed on the error?
0
 

Author Comment

by:tracyms
ID: 39935371
gowflow - one more thing, when I run your code (click the button) my other vb scripts don't fire in my project and I'm looking into that as well...
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39935372
yes you must have put in K a string not a number.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39935384
Now I get what you did.

If you move my macro in a module then change in my macro FindEndDate
this line
Set WS = ActiveSheet

by this
Set WS = Sheets("SheetName")

where SheetName is the name of the sheet that you moved in your workbook (originally Sheet1)

Also Make sure you also move the Sheet Trace as well.
gowflow
0
 

Author Comment

by:tracyms
ID: 39935502
Thanks gowflow - this is darn near perfect.

If you look at the attached Trace sheet, the start date of 3/3/2014 is 6 total hours, 3 working hours,  and 2 days (Mon/Tue) so the end date should be 3/4/2014...
Trace.pdf
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39935664
Hello tracyms,

For the formula you suggested you can't use

O10&P10&Q10&R10&S10&T10&U10

you need to specify the range like O10:U10 so the formula should be this:

=E10+SMALL(IF(ISNUMBER(MATCH(WEEKDAY(E10+ROW(INDIRECT("1:"&AC10*10))-1),IF(O10:U10<>"",{1,2,3,4,5,6,7}),0)),ROW(INDIRECT("1:"&AC10*10))-1),CEILING(AC10/((BT10-BS10)*24),1))

That formula needs to be "array entered" - I suspect you are getting #NUM! error because you aren't doing that.

To array enter you need to put the formula in a cell then select cell and press F2 key to select formula....then hold down CTRL and SHIFT keys while pressing ENTER. If done correctly you will see curly braces like { and } around the formula.

If you do that to the first formula you can copy down as normal - if you change the formula you need to repeat that process -see a working version of that formula attached, the first example is the last one you mentioned.

regards, barry
End-Date-barry4.xlsx
0
 

Author Comment

by:tracyms
ID: 39935886
Thanks Barry - no, I didn't know how to enter an array! It's working now - I see what you mean by the numbering for the serial days...would be good to start at Monday but not a show stopper...no way around that huh? :-)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39936102
yes don't put a zero it consider it as a day if there is something there it should be blank.
if you have a formula there put it this way:

instead of =G5=Sheet1!M5

have it test first if there is a value
IF(Sheet1!M5<>"",G5=Sheet1!M5,"")

Replace all the Mon to Sun refrences to your original sheet to be these ones.
gowflow
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 150 total points
ID: 39937216
Hello tracyms,

The order of the days can be anything you want - to make it Monday to Sunday just change the {1,2,3,4,5,6,7} part to {2,3,4,5,6,7,1} like this

=E10+SMALL(IF(ISNUMBER(MATCH(WEEKDAY(E10+ROW(INDIRECT("1:"&AC10*10))-1),IF(O10:U10<>"",{2,3,4,5,6,7,1}),0)),ROW(INDIRECT("1:"&AC10*10))-1),CEILING(AC10/((BT10-BS10)*24),1))

Now any value in O10 includes Monday, any value in P10 includes Tuesday etc.

regards, barry
0
 

Author Comment

by:tracyms
ID: 39938449
Cool. Thanks Barry!

gowflow,

I'm sending your 2007 sheet back to you to show it's doing the same thing - I only entered one start date with no holidays and its still adding a day, see attached.

I can work with what Barry has sent but would like to get yours working - I see I'm going to have to give points to both of you for doing such an outstanding job! I understand if you don't want to put any extra effort into it - I will appreciate it anyway! :-)
End-Date2007-Returned.xlsm
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39938515
Hello tracyms,

No problem - please ask if you have any more questions - this question really isn't long enough yet :)

regards, barry
0
 
LVL 29

Accepted Solution

by:
gowflow earned 350 total points
ID: 39938919
I see my mistake sorry, you had specified that the first day if falls on a workday should be counted as a day and I had overlooked this totally. Here it is fixed in this version.

@Barry still don't understand the difference of 1 day between your and mine.

OOPS !!! Correction just noticed the days sequence as you mentioned in the last post by changing your to {2,3,4,5,6,7,1} it comes right on.

gowflow
End-Date2007-V01.xlsm
0
 

Author Closing Comment

by:tracyms
ID: 39939608
Excellent solutions! I gave gowflow the edge as the trace feature was a super addition to track the dates. Barry, I am just as pleased with your solution as it was simple like I asked. Great job and THANK YOU BOTH!!!!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39939672
Your welcome, and thanks for asking as believe it or not we also gain from answering I personally not at all good in formulas and had an opportunity to learn 2 new things here:
SMALL and CEILING which I totally ignored and am amazed on how few functions could be so powerful !

Tks Barry
gowflow
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
This is about my first experience with programming Arduino.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now