Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
mm/dd/yy vs Mon, Tue, Wed etc
Excel2003 ...
Col A contains dates ... Format = mm/dd/yy Cols B thru H contain ... Text = Mon, Tue, Wed, Thu, Fri, Sat, Sun I wish to format & setup so I can have a formula under each day of the week that will peg the day vs the mm/dd/yy date in Col A. If Col A = 01/17/07 ... No-No-Yes-No-No-No-No ... (Yes = Weds) If Col A = 01/19/07 ... No-No-No-No-Yes-No-No ... (Yes = Fri) If Col A = 03/05/07 ... Yes-No-No-No-No-No-No ... (Yes = Mon) Thanks ... Kha |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
mm/dd/yy vs Mon, Tue, Wed etc
in the Monday columns,
=if(WEEKDAY(A1,2)=1,Yes,No) in tuesday, same, but =2,Yes,No etc. "Ken" wrote: Excel2003 ... Col A contains dates ... Format = mm/dd/yy Cols B thru H contain ... Text = Mon, Tue, Wed, Thu, Fri, Sat, Sun I wish to format & setup so I can have a formula under each day of the week that will peg the day vs the mm/dd/yy date in Col A. If Col A = 01/17/07 ... No-No-Yes-No-No-No-No ... (Yes = Weds) If Col A = 01/19/07 ... No-No-No-No-Yes-No-No ... (Yes = Fri) If Col A = 03/05/07 ... Yes-No-No-No-No-No-No ... (Yes = Mon) Thanks ... Kha |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
mm/dd/yy vs Mon, Tue, Wed etc
Kha,
Why not just have a formula =A5 in B5 and custome format B5 as "ddd" (without the quotes), it will return the three letter day of the week for the date. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Ken" wrote in message ... Excel2003 ... Col A contains dates ... Format = mm/dd/yy Cols B thru H contain ... Text = Mon, Tue, Wed, Thu, Fri, Sat, Sun I wish to format & setup so I can have a formula under each day of the week that will peg the day vs the mm/dd/yy date in Col A. If Col A = 01/17/07 ... No-No-Yes-No-No-No-No ... (Yes = Weds) If Col A = 01/19/07 ... No-No-No-No-Yes-No-No ... (Yes = Fri) If Col A = 03/05/07 ... Yes-No-No-No-No-No-No ... (Yes = Mon) Thanks ... Kha |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
mm/dd/yy vs Mon, Tue, Wed etc
maybe i am confused by this ...
If Col A = 01/17/07 ... Cols B thru H contain ... Text = Mon, Tue, Wed, Thu, Fri, Sat, Sun [e.g. B1:H1] on next column B same row. (ie. B2) =IF(TEXT(WEEKDAY($A2),"DDD")=B$1,"Yes","No") copy paste on rows and columns. ***** birds of the same feather flock together.. "Ken" wrote: Excel2003 ... Col A contains dates ... Format = mm/dd/yy Cols B thru H contain ... Text = Mon, Tue, Wed, Thu, Fri, Sat, Sun I wish to format & setup so I can have a formula under each day of the week that will peg the day vs the mm/dd/yy date in Col A. If Col A = 01/17/07 ... No-No-Yes-No-No-No-No ... (Yes = Weds) If Col A = 01/19/07 ... No-No-No-No-Yes-No-No ... (Yes = Fri) If Col A = 03/05/07 ... Yes-No-No-No-No-No-No ... (Yes = Mon) Thanks ... Kha |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
mm/dd/yy vs Mon, Tue, Wed etc
Hi,
Another way: assuming your days in B1:H1 are abreviated (Sun, Mon...etc) then: =IF(LEFT(TEXT($A2,"dddd"),3)=B$1,"Yes","No") if (Monday, Tuesday...etc) IF(TEXT($A2,"dddd")=B$1,"Yes","No") copied across and down HTH Jean-Guy "Ken" wrote: Excel2003 ... Col A contains dates ... Format = mm/dd/yy Cols B thru H contain ... Text = Mon, Tue, Wed, Thu, Fri, Sat, Sun I wish to format & setup so I can have a formula under each day of the week that will peg the day vs the mm/dd/yy date in Col A. If Col A = 01/17/07 ... No-No-Yes-No-No-No-No ... (Yes = Weds) If Col A = 01/19/07 ... No-No-No-No-Yes-No-No ... (Yes = Fri) If Col A = 03/05/07 ... Yes-No-No-No-No-No-No ... (Yes = Mon) Thanks ... Kha |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
mm/dd/yy vs Mon, Tue, Wed etc
Hi Sandy
Like Homer would say..... Dho!!! :) "Sandy Mann" wrote: pinmaster, assuming your days in B1:H1 are abreviated (Sun, Mon...etc) then: if so then you don't need the LEFT() Function: =IF(TEXT($A2,"ddd")=B$1,"Yes","No") -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "pinmaster" wrote in message ... Hi, Another way: assuming your days in B1:H1 are abreviated (Sun, Mon...etc) then: =IF(LEFT(TEXT($A2,"dddd"),3)=B$1,"Yes","No") if (Monday, Tuesday...etc) IF(TEXT($A2,"dddd")=B$1,"Yes","No") copied across and down HTH Jean-Guy "Ken" wrote: Excel2003 ... Col A contains dates ... Format = mm/dd/yy Cols B thru H contain ... Text = Mon, Tue, Wed, Thu, Fri, Sat, Sun I wish to format & setup so I can have a formula under each day of the week that will peg the day vs the mm/dd/yy date in Col A. If Col A = 01/17/07 ... No-No-Yes-No-No-No-No ... (Yes = Weds) If Col A = 01/19/07 ... No-No-No-No-Yes-No-No ... (Yes = Fri) If Col A = 03/05/07 ... Yes-No-No-No-No-No-No ... (Yes = Mon) Thanks ... Kha |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
mm/dd/yy vs Mon, Tue, Wed etc
AWESOME ... Its Tues & this works perfect ... I am not really doing what is
in my originally posted example ... However, it was an example to get the solution I needed for some Conditional Formatting ... And this is it ... Thanks ... Kha "Sean Timmons" wrote: in the Monday columns, =if(WEEKDAY(A1,2)=1,Yes,No) in tuesday, same, but =2,Yes,No etc. "Ken" wrote: Excel2003 ... Col A contains dates ... Format = mm/dd/yy Cols B thru H contain ... Text = Mon, Tue, Wed, Thu, Fri, Sat, Sun I wish to format & setup so I can have a formula under each day of the week that will peg the day vs the mm/dd/yy date in Col A. If Col A = 01/17/07 ... No-No-Yes-No-No-No-No ... (Yes = Weds) If Col A = 01/19/07 ... No-No-No-No-Yes-No-No ... (Yes = Fri) If Col A = 03/05/07 ... Yes-No-No-No-No-No-No ... (Yes = Mon) Thanks ... Kha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If a range of cells in Excel are formatted as dates ie. (mm/dd/yy) | Excel Discussion (Misc queries) | |||
Create a date column (MM/DD/YY) that fill sout automatically? | Excel Worksheet Functions | |||
How do I change dates from dd/mm/yy to mm/dd/yy | Setting up and Configuration of Excel | |||
HOW DO I FORMAT DATA FROM YY/MM/DD TO MM/DD/YY? | Excel Worksheet Functions | |||
HOW DO I FORMAT DATA FROM YY/MM/DD TO MM/DD/YY? | Excel Worksheet Functions |