Date calculation
Posted by Thomas on October 26, 2001 10:49 AM
I have a problem to write down a formula of counting dates.
Suppose I want the date of 16 days later locates on a business day.I write down the formula A1+16 in a cell. How do I develop the formula lacates on a business day.
Here is an example:
if today is 10/25/01,16 days later would be locates on the Saturday,but I want the result locates on the first following business day, which is 11/10/01. Today is 10/26/01,16 days later would be locates on Sunday,but I want the result shows the following business day,which is 11/10/01. Same as the previous one. Of course, there are some holidays issue need to be considered. Is there any add-in functions I can use in Excel? OR How do I develop a formula for that?Or should I write down a Macro for this?
Thanks for thrift respond.
Thomas
Posted by Mark W. on October 26, 2001 11:02 AM
=A1+16+CHOOSE(WEEKDAY(A1+16),1,0,0,0,0,0,2)
Posted by Mark W. on October 26, 2001 11:07 AM
Also, you ought to examine the WORKDAY function...
It's available for use once the Analysis ToolPak
has been added...
Posted by Thomas on October 26, 2001 11:13 AM
THANKS, That helps.
However, what if the dates also locates on a holiday,say Thanksgiving or Chrismas?
Posted by Mark W. on October 26, 2001 11:18 AM
That's why I think you should be looking at WORKDAY...
...however, WORKDAY's 2nd argument in expressed
in terms of workdays rather then calendar days.
Will that work for you? THANKS, That helps.
Posted by Thomas on October 26, 2001 11:33 AM
Re: That's why I think you should be looking at WORKDAY...
I'm sorry I don't get it.
How do I change the argument?
(I use the name box function to collect all the holidays as symbol as 'H').
Posted by Thomas on October 26, 2001 11:46 AM
I had it installed
i need a VB code that will import the data from an excel sheet into a SQL server table
Posted by Thomas on October 26, 2001 11:48 AM
I had the analysis tool installed and I use workday function in somewhere else.
Posted by Mark W. on October 26, 2001 11:50 AM
I was wondering...
...if you could look forward something like 22
workdays (i.e., 16+TRUNC(16/7)*2+MOD(16,7)) rather
than 16 calendar days. I'm sorry I don't get it.
Posted by Thomas on October 26, 2001 11:51 AM
I don't get it?
I'm sorry I don't get it.
How do I change the argument?
(I use the name box function to collect all the holidays as symbol as 'H').And also, I had the analysis tool installed and I use workday function somewhere else.
Could you help me out,please? ...however, WORKDAY's 2nd argument in expressed
Posted by Mark W. on October 26, 2001 11:55 AM
Oops!...
...didn't have my thinking cap on... it would be
more like 20 workdays (i.e, =16+TRUNC(16/7)*2)
rather than 16 calendar days! ...if you could look forward something like 22
Posted by Mark W. on October 26, 2001 12:00 PM
That still wasn't quite right...
...I'm in too big of a rush... must go to a meeting
right now... but, it would be more like 12 workdays
(i.e, =16-TRUNC(16/7)*2) rather than 16 calendar days!
This transformation is quite right either... it's off
by 1 day, and if fact may not be calculable. I
need to think a bit more about it! : ) ...didn't have my thinking cap on... it would be
Posted by Thomas on October 26, 2001 12:09 PM
Re: That still wasn't quite right...
Thanks,Mark. That's a big help.
I need some time to think about the complete formula again. I really appreciate that.
Thomas
Posted by Aladin Akyurek on October 26, 2001 12:32 PM
Re: That still wasn't quite right...
Isn't simply
=WORKDAY(A1,16,E1:E10)
where E1:E10 houses the holiday dates and A1 the input day?
Aladin
Posted by Thomas on October 26, 2001 12:55 PM
Re: That still wasn't quite right...We can't use workday...
I think we can't use workday function because it will ignore the holiday it pass through.For instance:
If the date is 11/09/01. 16 days later will be 11/25/01. and the result--11/26/01 in the first fomula you wrote is correct. However, the workday function will be(workday(11/09/01,12)=11/28/01).It's way too much.Any other idea?
Posted by Thomas on October 26, 2001 1:05 PM
Not the answer I want
The main point I am asking is to count the date of 16 days later. If the date falls on the weekend or on a holiday,it will shows up the following business date.However, the workday function will pass the weekend and holiday and shows the wrong result.
Any idea?
Posted by Aladin Akyurek on October 26, 2001 2:08 PM
Re: Not the answer I want
Thomas,
Care to post the set of holiday dates you have? And an example date that WORKDAY appears to treat wrong along with the expected result?
Aladin
Posted by Mark W. on October 29, 2001 7:19 AM
Thomas, I got it!!! I got it!!!
Sorry if you felt abandoned on Friday. At that
time (when I had to rush to attend a meeting) I was
flailing about trying to transform a calendar count
into a workday count so you could use the WORKDAY
function with its built-in holiday logic.
It wasn't until this weekend when it dawned on me
that I could use NETWORKDAYS (duh!) to accomplish
this translation. Suppose cell A1 contains
10/26/01 and cell B1 contains the number of
calendar days (e.g., 16) that you want to calc-
ulate forward. This formula will do the trick...
=WORKDAY(A1,NETWORKDAYS(A1,A1+B1),{"11/22/01","12/25/01"}+0)
All you need to do is list all of your notable
holidays (as text representations of date values)
in the array constant shown above.
If A1 contains 10/26/01 the results are Monday,
11/12/01. If A1 contains 12//9/01 the results
are Wednesday, 12/26. I think we can't use workday function because it will ignore the holiday it pass through.For instance:
Posted by Aladin Akyurek on October 29, 2001 7:45 PM
Mark, it appears...
that the following (longish) formula
=WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+$B$1),0,HOLIDAYS)+IF(WEEKDAY(WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+$B$1),0,HOLIDAYS),2)=6,2,IF(WEEKDAY(WORKDAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+$B$1),0,HOLIDAYS),2)=7,1,0))
produces the desired business day according to Thomas's specs.
HOLIDAYS that I used to test this is:
{36892;36906;36941;37039;37076;37137;37172;37206;37217;37250}
Test data along with results:
{37202,16,37218,"Friday";37203,"",37221,"Monday";37204,"",37221,"Monday";37205,"",37221,"Monday";37206,"",37222,"Tuesday"}
where test dates are in the first column.
Aladin
