Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

Populate an End Date in Excel

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
tracyms
Asked:
tracyms
  • 24
  • 22
  • 9
  • +1
2 Solutions
 
gowflowCommented:
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
 
tracymsAuthor Commented:
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
 
gowflowCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
tracymsAuthor Commented:
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
 
gowflowCommented:
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
 
tracymsAuthor Commented:
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
 
tracymsAuthor Commented:
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
 
gowflowCommented:
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
 
tracymsAuthor Commented:
I'm sorry about messing up the serial day numbers - :-( - yes we are in agreement. Thank you!
0
 
gowflowCommented:
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
 
tracymsAuthor Commented:
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
 
gowflowCommented:
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
 
tracymsAuthor Commented:
Nothings easy huh? :-) Let me think about this, ideally a formula to drag down would be better. Thanks.
0
 
tracymsAuthor Commented:
What if I didn't want the holidays? Would that work better as a formula?
0
 
gowflowCommented:
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
 
gowflowCommented:
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
 
barry houdiniCommented:
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
 
tracymsAuthor Commented:
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
 
barry houdiniCommented:
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
 
Ejgil HedegaardCommented:
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
 
gowflowCommented:
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
 
gowflowCommented:
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
 
barry houdiniCommented:
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
 
gowflowCommented:
Barry

hgholt made the comment to you about holiday not me !
gowflow
0
 
tracymsAuthor Commented:
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
 
tracymsAuthor Commented:
Wow - many posts since I wrote the above, I'm reading through them now.
0
 
tracymsAuthor Commented:
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
 
gowflowCommented:
what version of Excel you have?
gowflow
0
 
tracymsAuthor Commented:
Excel 2007
0
 
gowflowCommented:
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
 
tracymsAuthor Commented:
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
 
tracymsAuthor Commented:
Great, thanks for posting 2007 version - can I use the 2007 version in 2010 - I know I can do the other way around.
0
 
tracymsAuthor Commented:
Never mind, the 2007 version works in 2010. :-) Give me a minute to process what I have - thanks guys.
0
 
barry houdiniCommented:
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
 
gowflowCommented:
Barry don't worry, no sweat, no problem.
gowflow
0
 
tracymsAuthor Commented:
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
 
barry houdiniCommented:
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
 
gowflowCommented:
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
 
gowflowCommented:
@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
 
tracymsAuthor Commented:
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
 
gowflowCommented:
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
 
barry houdiniCommented:
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
 
tracymsAuthor Commented:
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
 
tracymsAuthor Commented:
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
 
gowflowCommented:
yes you must have put in K a string not a number.
gowflow
0
 
gowflowCommented:
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
 
tracymsAuthor Commented:
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
 
barry houdiniCommented:
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
 
tracymsAuthor Commented:
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
 
gowflowCommented:
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
 
barry houdiniCommented:
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
 
tracymsAuthor Commented:
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
 
barry houdiniCommented:
Hello tracyms,

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

regards, barry
0
 
gowflowCommented:
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
 
tracymsAuthor Commented:
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
 
gowflowCommented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 24
  • 22
  • 9
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now