Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday?
Hope the subject line isn't totally muddy <g.
I have a spreadsheet that is super. The only trouble is that weekends aren't differentiated from weekdays in any way. Since the spreadsheet contains a macro for the user to select the starting date and then the rest of the spreadsheet is then populated for 3 weeks following that date, Saturday and Sunday always fall on different cells at any given time. Also, affected cells may say "Saturday" or "Sunday" but there is actually only a formula in any of them. The only exception is the very first cell which is the initial one the macro dumps to. So the first might read "Friday" if I choose today's date in the prompt box, but all the rest of the days of the weeks shown display the dates according to appropriate variations of this formula: =IF(A2<"",A2+1,"") In today's example, this actual formula above displays "Saturday". The cell immediately below displays "Sep.17.2005". I'm hoping that since it's the same type formatting issue, hopefully resolving cell colouring for the text "Saturday" issue will lead me to figure out how to do so for dates that fall on weekends. Is there a way to do this cell colour change under these conditions? I'm in favour of conditional formatting, but couldn't figure out how to do so because of the fact above that no actual text is found where all the Saturdays and Sundays are, only the formula. Tx.! :oD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday?
Look at Excel's WEEKDAY function, eg
=WEEKDAY(mydate,3) If it returns 0-4 it's a weekday. Lends itself nicely to conditional formatting. Regards, Peter T "StargateFanFromWork" wrote in message ... Hope the subject line isn't totally muddy <g. I have a spreadsheet that is super. The only trouble is that weekends aren't differentiated from weekdays in any way. Since the spreadsheet contains a macro for the user to select the starting date and then the rest of the spreadsheet is then populated for 3 weeks following that date, Saturday and Sunday always fall on different cells at any given time. Also, affected cells may say "Saturday" or "Sunday" but there is actually only a formula in any of them. The only exception is the very first cell which is the initial one the macro dumps to. So the first might read "Friday" if I choose today's date in the prompt box, but all the rest of the days of the weeks shown display the dates according to appropriate variations of this formula: =IF(A2<"",A2+1,"") In today's example, this actual formula above displays "Saturday". The cell immediately below displays "Sep.17.2005". I'm hoping that since it's the same type formatting issue, hopefully resolving cell colouring for the text "Saturday" issue will lead me to figure out how to do so for dates that fall on weekends. Is there a way to do this cell colour change under these conditions? I'm in favour of conditional formatting, but couldn't figure out how to do so because of the fact above that no actual text is found where all the Saturdays and Sundays are, only the formula. Tx.! :oD |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday?
You mentioned that you already have a macro on this spreadsheet to pick up
the first date. If you were to run the following macro after you had completed the selection I think it will do what you want. Public Sub COLOURCELL() For Each c In Range("workingdays") c.Select If Weekday(ActiveCell.Value) = 1 Or Weekday(ActiveCell.Value) = 7 Then With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If Next c End Sub June "StargateFanFromWork" wrote in message ... Hope the subject line isn't totally muddy <g. I have a spreadsheet that is super. The only trouble is that weekends aren't differentiated from weekdays in any way. Since the spreadsheet contains a macro for the user to select the starting date and then the rest of the spreadsheet is then populated for 3 weeks following that date, Saturday and Sunday always fall on different cells at any given time. Also, affected cells may say "Saturday" or "Sunday" but there is actually only a formula in any of them. The only exception is the very first cell which is the initial one the macro dumps to. So the first might read "Friday" if I choose today's date in the prompt box, but all the rest of the days of the weeks shown display the dates according to appropriate variations of this formula: =IF(A2<"",A2+1,"") In today's example, this actual formula above displays "Saturday". The cell immediately below displays "Sep.17.2005". I'm hoping that since it's the same type formatting issue, hopefully resolving cell colouring for the text "Saturday" issue will lead me to figure out how to do so for dates that fall on weekends. Is there a way to do this cell colour change under these conditions? I'm in favour of conditional formatting, but couldn't figure out how to do so because of the fact above that no actual text is found where all the Saturdays and Sundays are, only the formula. Tx.! :oD |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday?
This weekday function looked promising but the same trouble may exist as
befo the dates will always vary in any given cell. B2 may have any day of the week. When I finally got a working formula - at least, it returns a value - I get Thursday instead of Saturday. I modified this: =IF(A2<"",A2+1,"") to what would work, which happened to be this: =IF(A2<"",WEEKDAY(A2+1,3),"") And in the cell that is formatted to show date in text format, it says Thursday instead of Saturday. Did I do something wrong? "Peter T" <peter_t@discussions wrote in message ... Look at Excel's WEEKDAY function, eg =WEEKDAY(mydate,3) If it returns 0-4 it's a weekday. Lends itself nicely to conditional formatting. Regards, Peter T "StargateFanFromWork" wrote in message ... Hope the subject line isn't totally muddy <g. I have a spreadsheet that is super. The only trouble is that weekends aren't differentiated from weekdays in any way. Since the spreadsheet contains a macro for the user to select the starting date and then the rest of the spreadsheet is then populated for 3 weeks following that date, Saturday and Sunday always fall on different cells at any given time. Also, affected cells may say "Saturday" or "Sunday" but there is actually only a formula in any of them. The only exception is the very first cell which is the initial one the macro dumps to. So the first might read "Friday" if I choose today's date in the prompt box, but all the rest of the days of the weeks shown display the dates according to appropriate variations of this formula: =IF(A2<"",A2+1,"") In today's example, this actual formula above displays "Saturday". The cell immediately below displays "Sep.17.2005". I'm hoping that since it's the same type formatting issue, hopefully resolving cell colouring for the text "Saturday" issue will lead me to figure out how to do so for dates that fall on weekends. Is there a way to do this cell colour change under these conditions? I'm in favour of conditional formatting, but couldn't figure out how to do so because of the fact above that no actual text is found where all the Saturdays and Sundays are, only the formula. Tx.! :oD |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday?
I don't follow where/how your IF function comes into things. But one way or
another it should be possible to use the results of the WEEKDAY function to CF your cells. Look in help for the second argument in the function. I used value 3 so that Mon-Fr returns 0-4, making it easy to return True/false in another formula. Enter a date in A1, eg Ctrl-semicolon In B1, =TEXT(A1,"dddd") Apply CF to B1 like this Change "Cell value is" to "Formula is" =WEEKDAY($A1,3)<5 Apply some format Add condition-2, also "Formula is" =WEEKDAY($A1,3)4 apply a format Select A1:B1 Grab the small selection handle bottom right, drag down. You should see Sat-Sun with different format to Mon-Fri. As I said, I don't follow your IF function so you will need to adapt. Perhaps helper cells with the same formula as above, to return simply True/false. Note the row ref in the formula is relative (without a $) to allow you to copy the formula down. Regards, Peter T "StargateFanFromWork" wrote in message ... This weekday function looked promising but the same trouble may exist as befo the dates will always vary in any given cell. B2 may have any day of the week. When I finally got a working formula - at least, it returns a value - I get Thursday instead of Saturday. I modified this: =IF(A2<"",A2+1,"") to what would work, which happened to be this: =IF(A2<"",WEEKDAY(A2+1,3),"") And in the cell that is formatted to show date in text format, it says Thursday instead of Saturday. Did I do something wrong? "Peter T" <peter_t@discussions wrote in message ... Look at Excel's WEEKDAY function, eg =WEEKDAY(mydate,3) If it returns 0-4 it's a weekday. Lends itself nicely to conditional formatting. Regards, Peter T "StargateFanFromWork" wrote in message ... Hope the subject line isn't totally muddy <g. I have a spreadsheet that is super. The only trouble is that weekends aren't differentiated from weekdays in any way. Since the spreadsheet contains a macro for the user to select the starting date and then the rest of the spreadsheet is then populated for 3 weeks following that date, Saturday and Sunday always fall on different cells at any given time. Also, affected cells may say "Saturday" or "Sunday" but there is actually only a formula in any of them. The only exception is the very first cell which is the initial one the macro dumps to. So the first might read "Friday" if I choose today's date in the prompt box, but all the rest of the days of the weeks shown display the dates according to appropriate variations of this formula: =IF(A2<"",A2+1,"") In today's example, this actual formula above displays "Saturday". The cell immediately below displays "Sep.17.2005". I'm hoping that since it's the same type formatting issue, hopefully resolving cell colouring for the text "Saturday" issue will lead me to figure out how to do so for dates that fall on weekends. Is there a way to do this cell colour change under these conditions? I'm in favour of conditional formatting, but couldn't figure out how to do so because of the fact above that no actual text is found where all the Saturdays and Sundays are, only the formula. Tx.! :oD |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday?
Another possibility -
You say you are returning days as Text. Sat & Sun are the only days that start with an "S" - right? (in English at least). so you could change the first formula in my previous example to =LEFT($B1,1)="S" assuming day as text is in B1 and the first character in the cell relates to the day. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... I don't follow where/how your IF function comes into things. But one way or another it should be possible to use the results of the WEEKDAY function to CF your cells. Look in help for the second argument in the function. I used value 3 so that Mon-Fr returns 0-4, making it easy to return True/false in another formula. Enter a date in A1, eg Ctrl-semicolon In B1, =TEXT(A1,"dddd") Apply CF to B1 like this Change "Cell value is" to "Formula is" =WEEKDAY($A1,3)<5 Apply some format Add condition-2, also "Formula is" =WEEKDAY($A1,3)4 apply a format Select A1:B1 Grab the small selection handle bottom right, drag down. You should see Sat-Sun with different format to Mon-Fri. As I said, I don't follow your IF function so you will need to adapt. Perhaps helper cells with the same formula as above, to return simply True/false. Note the row ref in the formula is relative (without a $) to allow you to copy the formula down. Regards, Peter T "StargateFanFromWork" wrote in message ... This weekday function looked promising but the same trouble may exist as befo the dates will always vary in any given cell. B2 may have any day of the week. When I finally got a working formula - at least, it returns a value - I get Thursday instead of Saturday. I modified this: =IF(A2<"",A2+1,"") to what would work, which happened to be this: =IF(A2<"",WEEKDAY(A2+1,3),"") And in the cell that is formatted to show date in text format, it says Thursday instead of Saturday. Did I do something wrong? "Peter T" <peter_t@discussions wrote in message ... Look at Excel's WEEKDAY function, eg =WEEKDAY(mydate,3) If it returns 0-4 it's a weekday. Lends itself nicely to conditional formatting. Regards, Peter T "StargateFanFromWork" wrote in message ... Hope the subject line isn't totally muddy <g. I have a spreadsheet that is super. The only trouble is that weekends aren't differentiated from weekdays in any way. Since the spreadsheet contains a macro for the user to select the starting date and then the rest of the spreadsheet is then populated for 3 weeks following that date, Saturday and Sunday always fall on different cells at any given time. Also, affected cells may say "Saturday" or "Sunday" but there is actually only a formula in any of them. The only exception is the very first cell which is the initial one the macro dumps to. So the first might read "Friday" if I choose today's date in the prompt box, but all the rest of the days of the weeks shown display the dates according to appropriate variations of this formula: =IF(A2<"",A2+1,"") In today's example, this actual formula above displays "Saturday". The cell immediately below displays "Sep.17.2005". I'm hoping that since it's the same type formatting issue, hopefully resolving cell colouring for the text "Saturday" issue will lead me to figure out how to do so for dates that fall on weekends. Is there a way to do this cell colour change under these conditions? I'm in favour of conditional formatting, but couldn't figure out how to do so because of the fact above that no actual text is found where all the Saturdays and Sundays are, only the formula. Tx.! :oD |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday?
On Sun, 18 Sep 2005 17:02:09 +0100, "Peter T" <peter_t@discussions
wrote: OK I got your file and think I follow. We were so nearly there! First to clarify to others to avoid downloading your file. A1 contains a day name as a string, that of the date in A2 A2 contains a Date A3 is an empty cell A1 & A2 are populated with a macro (before the macro is run the cells are empty) A1 & A2, CF with medium grey and A3 with light grey, if A2 is a Sat or Sun and A2 actually has a date. This pattern of 3 cells is repeated 3*7 times with incremented dates by use of an If formulas ------------------ CF's for A1, A2 & A3 will have exactly the same formula using the "Formula Is" setting: =AND(WEEKDAY(A2,3)4,A2<"") Format colour for A1 & A2 medium grey, light grey for A3 Copy A1:A3 an pastespecial formats over the entire area, a multiple of 3 rows. Regards, Peter T Thank you. I will look at this tonight. At least you're still saying it's do-able! <lol |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set NETWORKDAYS to regard friday and saturday as weekend | Excel Worksheet Functions | |||
Determining the Sunday date of the third "full" weekend of a month | Excel Worksheet Functions | |||
Friday as weekend instead of Saturday & Sunday | Excel Worksheet Functions | |||
I want a particular cell to know the upcoming Saturday date. | Excel Discussion (Misc queries) | |||
Determine next Saturday in calendar based on date in a cell | New Users to Excel |