This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

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

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

"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 (

=IF(J2+ROUNDUP((K2/(HOUR(B

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

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

=IF(J2+ROUNDUP((K2/(HOUR(B

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

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.

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

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

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

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

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

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(K

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(K

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

regards, barry

End-Date-barry.xlsx

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

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

...second formula should be this in sheet 2

=WORKDAY.INTL(J2-1,CEILING

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(MATC

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

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
```

End-Date-barry2-with-VBA-functio.xlsm
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
```

Regards

gowflow

End-Date.xlsm

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

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

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.

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.Work

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

as per one of my comments above, all my suggested formulas will work with a

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

Apologies, I wrongly attributed that comment to you

regards, 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?

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(MATC

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(MATC

See attached examples

regards, barry

End-Date-barry3.xlsx

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

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.WorksheetFunct

gowflow

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(

IF(O10&P10&Q10&R10&S10&T10

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

=E10+SMALL(IF(ISNUMBER(MAT

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

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

In that last formula I posted, i.e.

=J2+SMALL(IF(ISNUMBER(MATC

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

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?

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

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

For the formula you suggested you can't use

O10&P10&Q10&R10&S10&T10&U1

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

=E10+SMALL(IF(ISNUMBER(MAT

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

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

Replace all the Mon to Sun refrences to your original sheet to be these ones.

gowflow

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(MAT

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

regards, 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

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

regards, barry

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

@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