| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round dates to weekdays
Is there a way that Excel can recognize if a date is a weekend and round it
to the nearest weekday? -- TIA, Nan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Round dates to weekdays
Using another cell and assume your date is in A1 --- =IF
(MOD(A1,7)<2,A1+2-MOD(A1,7),A1) "Nan" wrote: Is there a way that Excel can recognize if a date is a weekend and round it to the nearest weekday? -- TIA, Nan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Round dates to weekdays
=A10+(WEEKDAY(A10,2)5)+(WEEKDAY(A10)6)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nan" wrote in message ... Is there a way that Excel can recognize if a date is a weekend and round it to the nearest weekday? -- TIA, Nan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Round dates to weekdays
On Wed, 21 May 2008 13:12:00 -0700, Nan wrote:
Is there a way that Excel can recognize if a date is a weekend and round it to the nearest weekday? One way is to use a formula (with your date in A1): =A1-(WEEKDAY(A1)=7)+(WEEKDAY(A1)=1) --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Round dates to weekdays
One way...
A2 = some date =A2+CHOOSE(WEEKDAY(A2),1,0,0,0,0,0,-1) A Saturday date gets reduced to Friday's date and a Sunday date gets advanced to Monday's date. -- Biff Microsoft Excel MVP "Nan" wrote in message ... Is there a way that Excel can recognize if a date is a weekend and round it to the nearest weekday? -- TIA, Nan |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Round dates to weekdays
Thank you all for your great (and FAST!) replies. I know at least one will
do the trick for me! -- TIA, Nan "Nan" wrote: Is there a way that Excel can recognize if a date is a weekend and round it to the nearest weekday? -- TIA, Nan |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Round dates to weekdays
Maybe a little simpler...
=A1-(MOD(A1,7)=0)+(MOD(A1,7)=1) Rick "dhstein" wrote in message ... Using another cell and assume your date is in A1 --- =IF (MOD(A1,7)<2,A1+2-MOD(A1,7),A1) "Nan" wrote: Is there a way that Excel can recognize if a date is a weekend and round it to the nearest weekday? -- TIA, Nan |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
excel rounding to weekdays
I have a chart that is based on different formulas for each column. I want to keep the formulas I have and make sure the chart only displays weekdays.
I've found the formula =IF (MOD(A1,7)<2,A1+2-MOD(A1,7),A1) But I don't know how to add that to my pre-existing formulas. my existing formula for all rows in column A is =A#+5 in column B, my formula is =B#+3 etc. Each column is different. Can you help? dhstei wrote: Using another cell and assume your date is in A1 --- =IF 21-May-08 Using another cell and assume your date is in A1 --- =IF (MOD(A1,7)<2,A1+2-MOD(A1,7),A1) "Nan" wrote: Previous Posts In This Thread: On Wednesday, May 21, 2008 4:12 PM Na wrote: Round dates to weekdays Is there a way that Excel can recognize if a date is a weekend and round it to the nearest weekday? -- TIA, Nan On Wednesday, May 21, 2008 4:36 PM dhstei wrote: Using another cell and assume your date is in A1 --- =IF Using another cell and assume your date is in A1 --- =IF (MOD(A1,7)<2,A1+2-MOD(A1,7),A1) "Nan" wrote: On Wednesday, May 21, 2008 4:38 PM Bob Phillips wrote: =A10+(WEEKDAY(A10,2)5)+(WEEKDAY(A10)6)-- ---HTHBob(there's no email, no =A10+(WEEKDAY(A10,2)5)+(WEEKDAY(A10)6) -- --- HTH Bob (there is no email, no snail mail, but somewhere should be gmail in my addy) On Wednesday, May 21, 2008 4:38 PM Ron Rosenfeld wrote: Round dates to weekdays One way is to use a formula (with your date in A1): =A1-(WEEKDAY(A1)=7)+(WEEKDAY(A1)=1) --ron On Wednesday, May 21, 2008 4:48 PM T. Valko wrote: One way... One way... A2 = some date =A2+CHOOSE(WEEKDAY(A2),1,0,0,0,0,0,-1) A Saturday date gets reduced to Friday's date and a Sunday date gets advanced to Monday's date. -- Biff Microsoft Excel MVP On Wednesday, May 21, 2008 5:14 PM Na wrote: Thank you all for your great (and FAST! Thank you all for your great (and FAST!) replies. I know at least one will do the trick for me! -- TIA, Nan "Nan" wrote: On Wednesday, May 21, 2008 5:44 PM Rick Rothstein \(MVP - VB\) wrote: Maybe a little simpler... Maybe a little simpler... =A1-(MOD(A1,7)=0)+(MOD(A1,7)=1) Rick EggHeadCafe - Software Developer Portal of Choice Scriptless ASP Progress Indicator http://www.eggheadcafe.com/tutorials...rogress-i.aspx |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
excel rounding to weekdays
Not sure what you're doing. Maybe this...
=IF(MOD(A1+5,7)<2,A1+5+2-MOD(A1+5,7),A1+5) -- Biff Microsoft Excel MVP <amy benham wrote in message ... I have a chart that is based on different formulas for each column. I want to keep the formulas I have and make sure the chart only displays weekdays. I've found the formula =IF (MOD(A1,7)<2,A1+2-MOD(A1,7),A1) But I don't know how to add that to my pre-existing formulas. my existing formula for all rows in column A is =A#+5 in column B, my formula is =B#+3 etc. Each column is different. Can you help? dhstei wrote: Using another cell and assume your date is in A1 --- =IF 21-May-08 Using another cell and assume your date is in A1 --- =IF (MOD(A1,7)<2,A1+2-MOD(A1,7),A1) "Nan" wrote: Previous Posts In This Thread: On Wednesday, May 21, 2008 4:12 PM Na wrote: Round dates to weekdays Is there a way that Excel can recognize if a date is a weekend and round it to the nearest weekday? -- TIA, Nan On Wednesday, May 21, 2008 4:36 PM dhstei wrote: Using another cell and assume your date is in A1 --- =IF Using another cell and assume your date is in A1 --- =IF (MOD(A1,7)<2,A1+2-MOD(A1,7),A1) "Nan" wrote: On Wednesday, May 21, 2008 4:38 PM Bob Phillips wrote: =A10+(WEEKDAY(A10,2)5)+(WEEKDAY(A10)6)-- ---HTHBob(there's no email, no =A10+(WEEKDAY(A10,2)5)+(WEEKDAY(A10)6) -- --- HTH Bob (there is no email, no snail mail, but somewhere should be gmail in my addy) On Wednesday, May 21, 2008 4:38 PM Ron Rosenfeld wrote: Round dates to weekdays One way is to use a formula (with your date in A1): =A1-(WEEKDAY(A1)=7)+(WEEKDAY(A1)=1) --ron On Wednesday, May 21, 2008 4:48 PM T. Valko wrote: One way... One way... A2 = some date =A2+CHOOSE(WEEKDAY(A2),1,0,0,0,0,0,-1) A Saturday date gets reduced to Friday's date and a Sunday date gets advanced to Monday's date. -- Biff Microsoft Excel MVP On Wednesday, May 21, 2008 5:14 PM Na wrote: Thank you all for your great (and FAST! Thank you all for your great (and FAST!) replies. I know at least one will do the trick for me! -- TIA, Nan "Nan" wrote: On Wednesday, May 21, 2008 5:44 PM Rick Rothstein \(MVP - VB\) wrote: Maybe a little simpler... Maybe a little simpler... =A1-(MOD(A1,7)=0)+(MOD(A1,7)=1) Rick EggHeadCafe - Software Developer Portal of Choice Scriptless ASP Progress Indicator http://www.eggheadcafe.com/tutorials...rogress-i.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Difference (weekdays) between two dates | Excel Worksheet Functions | |||
| dates for weekdays in non-contiguous cells | Excel Worksheet Functions | |||
| How to generate a random list of weekDAYS between two dates? | Excel Worksheet Functions | |||
| How do i count number of weekdays between two dates? | Excel Worksheet Functions | |||
| Fill dates with weekdays/workdays only | Excel Worksheet Functions | |||
Linear Mode
