Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date
I have a series of dates in column f, and the formula is
DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to display 06/20/05, is that possible. Any help you can provide would be greatly appreciated. Thanks in advance |
#2
|
|||
|
|||
Hi David
there is a weekday function which tells you what day of the week an event is (by default it puts Sunday as 1) so you could do something like this: =DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0)) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "David" wrote in message ... I have a series of dates in column f, and the formula is DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to display 06/20/05, is that possible. Any help you can provide would be greatly appreciated. Thanks in advance |
#3
|
|||
|
|||
I think it could be better in this way:
=IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, 2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1 ,1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))) Bye Franz "JulieD" ha scritto nel messaggio ... Hi David there is a weekday function which tells you what day of the week an event is (by default it puts Sunday as 1) so you could do something like this: =DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0)) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "David" wrote in message ... I have a series of dates in column f, and the formula is DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to display 06/20/05, is that possible. Any help you can provide would be greatly appreciated. Thanks in advance |
#4
|
|||
|
|||
Hi Franz
why better? -- Cheers JulieD "Franz" wrote in message ... I think it could be better in this way: =IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, 2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1 ,1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))) Bye Franz "JulieD" ha scritto nel messaggio ... Hi David there is a weekday function which tells you what day of the week an event is (by default it puts Sunday as 1) so you could do something like this: =DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0)) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "David" wrote in message ... I have a series of dates in column f, and the formula is DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to display 06/20/05, is that possible. Any help you can provide would be greatly appreciated. Thanks in advance |
#5
|
|||
|
|||
Ooops...
sorry... This should work =IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)+2,IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,DATE(YEAR(F3),M ONTH(F3)+1,DAY(F3)+1,DATE(YEAR(F3),MONTH(F3)+1,DAY (F3))) "Franz" ha scritto nel messaggio ... I think it could be better in this way: =IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, 2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1 ,1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))) Bye Franz "JulieD" ha scritto nel messaggio ... Hi David there is a weekday function which tells you what day of the week an event is (by default it puts Sunday as 1) so you could do something like this: =DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0)) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "David" wrote in message ... I have a series of dates in column f, and the formula is DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to display 06/20/05, is that possible. Any help you can provide would be greatly appreciated. Thanks in advance |
#6
|
|||
|
|||
On Mon, 18 Apr 2005 07:24:09 -0700, "David"
wrote: I have a series of dates in column f, and the formula is DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to display 06/20/05, is that possible. Any help you can provide would be greatly appreciated. Thanks in advance I would use a somewhat different approach. I have assumed that your "index date" is in F3. And that your formula for subsequent dates are in F4..Fn. I have also assumed, which may not be the case, that if your index date is, let us say, 31 Jan, that when you add one month you would like the result to be 28 (or 29) Feb, unless that date is a weekend in which case you would accept a March date. If those assumptions are correct, then I would use the formula: =WorKDAY(edate($F$3,ROW()-3)-1,1) If the WORKDAY or EDATE functions are not available, and return the #NAME? error, install and load the Analysis ToolPak add-in. On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program. If those assumptions are not correct, please post back. --ron |
#7
|
|||
|
|||
That is exactly right, but I have no idea how the formula is working, because
of $F$3 anchors. "Ron Rosenfeld" wrote: On Mon, 18 Apr 2005 07:24:09 -0700, "David" wrote: I have a series of dates in column f, and the formula is DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to display 06/20/05, is that possible. Any help you can provide would be greatly appreciated. Thanks in advance I would use a somewhat different approach. I have assumed that your "index date" is in F3. And that your formula for subsequent dates are in F4..Fn. I have also assumed, which may not be the case, that if your index date is, let us say, 31 Jan, that when you add one month you would like the result to be 28 (or 29) Feb, unless that date is a weekend in which case you would accept a March date. If those assumptions are correct, then I would use the formula: =WorKDAY(edate($F$3,ROW()-3)-1,1) If the WORKDAY or EDATE functions are not available, and return the #NAME? error, install and load the Analysis ToolPak add-in. On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program. If those assumptions are not correct, please post back. --ron |
#8
|
|||
|
|||
Hi Julie
I'm sorry... Sometimes I should read better... Your formula was very good Hope you will forgive me Also for my English... Bye Franz "JulieD" ha scritto nel messaggio ... Hi Franz why better? -- Cheers JulieD "Franz" wrote in message ... I think it could be better in this way: =IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, 2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1 ,1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))) Bye Franz "JulieD" ha scritto nel messaggio ... Hi David there is a weekday function which tells you what day of the week an event is (by default it puts Sunday as 1) so you could do something like this: =DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0)) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "David" wrote in message ... I have a series of dates in column f, and the formula is DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to display 06/20/05, is that possible. Any help you can provide would be greatly appreciated. Thanks in advance |
#9
|
|||
|
|||
|
#10
|
|||
|
|||
Hi Franz
no apology necessary .... -- Cheers JulieD "Franz" wrote in message ... Hi Julie I'm sorry... Sometimes I should read better... Your formula was very good Hope you will forgive me Also for my English... Bye Franz "JulieD" ha scritto nel messaggio ... Hi Franz why better? -- Cheers JulieD "Franz" wrote in message ... I think it could be better in this way: =IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, 2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1 ,1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))) Bye Franz "JulieD" ha scritto nel messaggio ... Hi David there is a weekday function which tells you what day of the week an event is (by default it puts Sunday as 1) so you could do something like this: =DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0)) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "David" wrote in message ... I have a series of dates in column f, and the formula is DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to display 06/20/05, is that possible. Any help you can provide would be greatly appreciated. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date and time | New Users to Excel | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |