| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formula
I have the following formula I created and was wondering instead of it
completing each consecutive weekday is there a way to have it take a date 5-5-08 and use that same day every 5 lines then create the next weekday add that to 5 lines and continue down 1700 records??? =IF(WEEKDAY(K2)=7,K2+2,IF(WEEKDAY(K2)=6,K2+3,K2+1) ) - Where cell K2 is the start date. I have a 1700 record spreadsheet that instead of keying a date myself every 5 records having a formula do it for me. Please Help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
Not sure what you're wanting but...
A1 = 5/5/2008 (m/d/y) Enter this formula in A2: =DATE(YEAR(A$1),MONTH(A$1),DAY(A$1-1)+CEILING(ROWS(A$1:A2)/5,1)) When copied down, the formula will repeat each date 5 times then increment by 1 day: A1 = 5/5/2008 A2 = 5/5/2008 A3 = 5/5/2008 A4 = 5/5/2008 A5 = 5/5/2008 A6 = 5/6/2008 A7 = 5/6/2008 A8 = 5/6/2008 A9 = 5/6/2008 A10 = 5/6/2008 A11 = 5/7/2008 etc -- Biff Microsoft Excel MVP "Brooke" wrote in message ... I have the following formula I created and was wondering instead of it completing each consecutive weekday is there a way to have it take a date 5-5-08 and use that same day every 5 lines then create the next weekday add that to 5 lines and continue down 1700 records??? =IF(WEEKDAY(K2)=7,K2+2,IF(WEEKDAY(K2)=6,K2+3,K2+1) ) - Where cell K2 is the start date. I have a 1700 record spreadsheet that instead of keying a date myself every 5 records having a formula do it for me. Please Help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
Try this:
K2: 5/5/2008 K3: =IF(COUNTIF(K$2:K2,K2)<5,K2,WORKDAY(K2,1)) "Brooke" wrote: I have the following formula I created and was wondering instead of it completing each consecutive weekday is there a way to have it take a date 5-5-08 and use that same day every 5 lines then create the next weekday add that to 5 lines and continue down 1700 records??? =IF(WEEKDAY(K2)=7,K2+2,IF(WEEKDAY(K2)=6,K2+3,K2+1) ) - Where cell K2 is the start date. I have a 1700 record spreadsheet that instead of keying a date myself every 5 records having a formula do it for me. Please Help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
Your formula is including Sat, and Sun. OP doesn't want to include Sat, and
Sun based on his/her formula "T. Valko" wrote: Not sure what you're wanting but... A1 = 5/5/2008 (m/d/y) Enter this formula in A2: =DATE(YEAR(A$1),MONTH(A$1),DAY(A$1-1)+CEILING(ROWS(A$1:A2)/5,1)) When copied down, the formula will repeat each date 5 times then increment by 1 day: A1 = 5/5/2008 A2 = 5/5/2008 A3 = 5/5/2008 A4 = 5/5/2008 A5 = 5/5/2008 A6 = 5/6/2008 A7 = 5/6/2008 A8 = 5/6/2008 A9 = 5/6/2008 A10 = 5/6/2008 A11 = 5/7/2008 etc -- Biff Microsoft Excel MVP "Brooke" wrote in message ... I have the following formula I created and was wondering instead of it completing each consecutive weekday is there a way to have it take a date 5-5-08 and use that same day every 5 lines then create the next weekday add that to 5 lines and continue down 1700 records??? =IF(WEEKDAY(K2)=7,K2+2,IF(WEEKDAY(K2)=6,K2+3,K2+1) ) - Where cell K2 is the start date. I have a 1700 record spreadsheet that instead of keying a date myself every 5 records having a formula do it for me. Please Help. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
Copy down as far as needed
"Teethless mama" wrote: Try this: K2: 5/5/2008 K3: =IF(COUNTIF(K$2:K2,K2)<5,K2,WORKDAY(K2,1)) "Brooke" wrote: I have the following formula I created and was wondering instead of it completing each consecutive weekday is there a way to have it take a date 5-5-08 and use that same day every 5 lines then create the next weekday add that to 5 lines and continue down 1700 records??? =IF(WEEKDAY(K2)=7,K2+2,IF(WEEKDAY(K2)=6,K2+3,K2+1) ) - Where cell K2 is the start date. I have a 1700 record spreadsheet that instead of keying a date myself every 5 records having a formula do it for me. Please Help. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
On Tue, 26 Feb 2008 12:58:01 -0800, Brooke
wrote: I have the following formula I created and was wondering instead of it completing each consecutive weekday is there a way to have it take a date 5-5-08 and use that same day every 5 lines then create the next weekday add that to 5 lines and continue down 1700 records??? =IF(WEEKDAY(K2)=7,K2+2,IF(WEEKDAY(K2)=6,K2+3,K2+1 )) - Where cell K2 is the start date. I have a 1700 record spreadsheet that instead of keying a date myself every 5 records having a formula do it for me. Please Help. You can try this formula: With your starting date in A1: A2: =WORKDAY($A$1,INT(ROWS($5:5)/5)) Fill down as far as required. Change $A$1 to refer to the cell reference of your starting date. ========================================== If the WORKDAY function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? 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. ================================ --ron |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
Not sure what you're wanting but...
-- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... Your formula is including Sat, and Sun. OP doesn't want to include Sat, and Sun based on his/her formula "T. Valko" wrote: Not sure what you're wanting but... A1 = 5/5/2008 (m/d/y) Enter this formula in A2: =DATE(YEAR(A$1),MONTH(A$1),DAY(A$1-1)+CEILING(ROWS(A$1:A2)/5,1)) When copied down, the formula will repeat each date 5 times then increment by 1 day: A1 = 5/5/2008 A2 = 5/5/2008 A3 = 5/5/2008 A4 = 5/5/2008 A5 = 5/5/2008 A6 = 5/6/2008 A7 = 5/6/2008 A8 = 5/6/2008 A9 = 5/6/2008 A10 = 5/6/2008 A11 = 5/7/2008 etc -- Biff Microsoft Excel MVP "Brooke" wrote in message ... I have the following formula I created and was wondering instead of it completing each consecutive weekday is there a way to have it take a date 5-5-08 and use that same day every 5 lines then create the next weekday add that to 5 lines and continue down 1700 records??? =IF(WEEKDAY(K2)=7,K2+2,IF(WEEKDAY(K2)=6,K2+3,K2+1) ) - Where cell K2 is the start date. I have a 1700 record spreadsheet that instead of keying a date myself every 5 records having a formula do it for me. Please Help. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
Improvement:
A1 = 5/5/2008 (m/d/y) Enter this formula in A2: =DATE(YEAR(A$1),MONTH(A$1),DAY(A$1-1)+CEILING(ROWS(A$1:A2)/5,1)) The formula can be reduced to: =A$1-1+CEILING(ROWS(A$1:A2)/5,1) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Not sure what you're wanting but... A1 = 5/5/2008 (m/d/y) Enter this formula in A2: =DATE(YEAR(A$1),MONTH(A$1),DAY(A$1-1)+CEILING(ROWS(A$1:A2)/5,1)) When copied down, the formula will repeat each date 5 times then increment by 1 day: A1 = 5/5/2008 A2 = 5/5/2008 A3 = 5/5/2008 A4 = 5/5/2008 A5 = 5/5/2008 A6 = 5/6/2008 A7 = 5/6/2008 A8 = 5/6/2008 A9 = 5/6/2008 A10 = 5/6/2008 A11 = 5/7/2008 etc -- Biff Microsoft Excel MVP "Brooke" wrote in message ... I have the following formula I created and was wondering instead of it completing each consecutive weekday is there a way to have it take a date 5-5-08 and use that same day every 5 lines then create the next weekday add that to 5 lines and continue down 1700 records??? =IF(WEEKDAY(K2)=7,K2+2,IF(WEEKDAY(K2)=6,K2+3,K2+1) ) - Where cell K2 is the start date. I have a 1700 record spreadsheet that instead of keying a date myself every 5 records having a formula do it for me. Please Help. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
On Tue, 26 Feb 2008 23:24:50 -0500, "T. Valko" wrote:
Improvement: A1 = 5/5/2008 (m/d/y) Enter this formula in A2: =DATE(YEAR(A$1),MONTH(A$1),DAY(A$1-1)+CEILING(ROWS(A$1:A2)/5,1)) The formula can be reduced to: =A$1-1+CEILING(ROWS(A$1:A2)/5,1) The OP requested to return only Weekdays (as did the OP's formula). Your suggestions also return Saturdays and Sundays. --ron |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
In my original reply I noted:
Not sure what you're wanting but... -- Biff Microsoft Excel MVP "Ron Rosenfeld" wrote in message ... On Tue, 26 Feb 2008 23:24:50 -0500, "T. Valko" wrote: Improvement: A1 = 5/5/2008 (m/d/y) Enter this formula in A2: =DATE(YEAR(A$1),MONTH(A$1),DAY(A$1-1)+CEILING(ROWS(A$1:A2)/5,1)) The formula can be reduced to: =A$1-1+CEILING(ROWS(A$1:A2)/5,1) The OP requested to return only Weekdays (as did the OP's formula). Your suggestions also return Saturdays and Sundays. --ron |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
On Wed, 27 Feb 2008 12:45:23 -0500, "T. Valko" wrote:
In my original reply I noted: Not sure what you're wanting but... -- Biff Microsoft Excel MVP Well, since he specifically requested WEEKdays, and since the formula he was using excluded Saturdays and Sundays, I guess I didn't have the same uncertainty on this point as you. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
| Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
| Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
| Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
| formula to calculate future date from date in cell plus days | Excel Worksheet Functions | |||
Linear Mode
