Calculate the Start date

[lyroj]

hi, i have a wookbook where user enters the end date of a job & it has to calculate the start date by considering the time required (in days) to do the job.
A1=Start date
B1=End date
C1=Time reqd (in days)

i used A1 = B1-C1 , to get the start date.

But i need a formula that does the following.

Start date = End date-(Time rqd + Number of sundays + no of holidays)
plz help.

 

[Sandeep Warrier]

Hi and welcome to the Board!

How about something like

Excel Workbook
	A	B	C	D
1	Start Date	04-11-2010		List of Holidays
2	End Date	07-12-2010		01-06-2010
3	No. of Days	28		04-09-2010
4				06-10-2010
5				28-10-2010
6				05-11-2010
7				06-12-2010
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B1	=B2-(B3+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B2-B3&":"&B2)))=1))+SUMPRODUCT(--ISNUMBER(MATCH($D$2:$D$7,ROW(INDIRECT(B2-B3&":"&B2)),0))))

 

[p45cal]

…or
=WORKDAY(B1,-C1,$G$1:$G$30)
where your list of holidays is in G1:G30.
You may want to add or subtract a day or two depending on whether the start and end dates are included as full working days or not (work compoleted by beginning of day of.. or wark starts before the end of.. and the like).

=WORKDAY(B1+1,-C1-1,$G$1:$G$30)
=WORKDAY(B1-1,-C1+1,$G$1:$G$30)

trial and error.

 

[lyroj]

thank u sandeep,
The formula works well.could u explain in detail what each step does?
thankz in advance.

 

[lyroj]

…or
=WORKDAY(B1,-C1,$G$1:$G$30)
where your list of holidays is in G1:G30.

It works to some extent.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
But the workday function considers sun & sat as weekends.<o></o>
We have a scenario where sat is a working day.

 

[Sandeep Warrier]

Hi lyroj,

=B2-(B3+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B2-B3&":"&B2)))=1))+SUMPRODUCT(--ISNUMBER(MATCH($D$2:$D$7,ROW(INDIRECT(B2-B3&":"&B2)),0))))


ROW(INDIRECT(B2-B3&":"&B2)) -> Since Excel stores dates as numbers, the INDIRECT function used with the ROW function creates an array of numbers from End Date - No. of days to End Date. Since this is used in SUMPRODUCT, and SUMPRODUCT accepts arrays, we don't have to enter this as an array formula.

The range of numbers when used with the WEEKDAY function gives another range of numbers 1-7. This is equated with 1 (for Sundays). This results in an array of TRUE/FALSE (True for Sundays and False for other days).

The -- but coerces the TRUE/FALSE to 1/0. SUMPRODUCT then gives the sum of the range = no. of Sundays that occur within the date range End Date - No. of days to End Date.

Similarly
MATCH($D$2:$D$7,ROW(INDIRECT(B2-B3&":"&B2)),0) where D2:D7 contains the holiday list matches the holiday list against the date range, returning the position in the array (if match occurs) and an error (if there is no match). The ISNUMBER part converts this array to an array of TRUE/FALSE for when matches are present and when they are not. -- once again coerces TRUE/FALSE to 1/0 and SUMPRODUCT returns the number of days from the holiday list that occur in the date range End Date - No. of days to End Date.

These two numbers are added to No. of days and the total is subtracted from End date resulting in the start date.

Hope this helps.

 

[barry houdini]

I don't always get the correct results with that formula. For example if end date is Thursday 9th December 2010 and days is 10 then I'd expect the answer to be Sat 27th Nov 2010 but formula gives Sun 28th.

I think you need to use an array formula, e.g. with end date in B1 and ime reqd (days) in C1 you can use this formula in A1

=B1-SMALL(IF(WEEKDAY(B1-ROW(INDIRECT("1:"&C1*10)))<>1,IF(ISNA(MATCH(B1-ROW(INDIRECT("1:"&C1*10)),H$1:H$10,0)),ROW(INDIRECT("1:"&C1*10)))),C1)

confirmed with CTRL+SHIFT+ENTER

where holidays are listed in H1:H10

 

[p45cal]

Excel 2010 has workday.intl
WORKDAY.INTL( start_date, days, [weekend], [holidays] )
http://office.microsoft.com/en-ca/excel-help/workday-intl-function-HA010354380.aspx

 

[Sandeep Warrier]

Hi Barry,

I understand what you mean. If the start date calculated by my formula lands on a Sunday it displays that day.

Can you please elaborate on why you used *10 in the formula?

If you consider the example in my earlier post.... your formula gives the result 02/11/2010.... while I believe the correct answer should be 04/11/2010.

For end date 07/12 and no. of days 28, 07/12 - 28 = 09/11. There are 4 sundays between 09/11 and 07/12 and one holiday (06/12) from the holiday list. So we need to subtract another 5 days from 09/11 resulting in 04/11..... (is the logic I applied correct?)

Excel Workbook
	A	B	C	D	E
1		Barry	Sandeep
2	Start Date	02-11-2010	04-11-2010		List of Holidays
3	End Date	07-12-2010			01-06-2010
4	No. of Days	28			04-09-2010
5					06-10-2010
6					28-10-2010
7					05-11-2010
8					06-12-2010
Sheet1

Excel 2010

Cell Formulas
Range	Formula
C2	=B3-(B4+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B3-B4&":"&B3)))=1))+SUMPRODUCT(--ISNUMBER(MATCH($E$3:$E$8,ROW(INDIRECT(B3-B4&":"&B3)),0))))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself



EDIT: Understood why the answer should be 02/11 and not 04/11....

 

[barry houdini]

Hello Sandeep,

Yes, I think the difficulty with this sort of question is that you don't know how many Sundays (or holidays) there are in the range until you know the start date!

My approach picks an arbitrary number of days into the past (C1*10) and then identifies which of those dates are working days....and then picks the C1th instance.

by using C1*10 the formula should work in all cases except where there are >= 10 consecutive non working days (and C1 is 1). I assume that's unlikely but to ensure that the formula works in (nearly) all cases you could replace the 10 with a count of holidays and some, e.g. (COUNT(H$1:H$10)+2) or similar