Conditional Format If Saturday Or Sunday
Oct 17, 2007
I am trying to format a row (B) if B8 = Saturday or Sunday.
The value in B8 is derived from a formula.
I have tried the following but it only works if cell is typed manually.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("B8")
If .Value = "Saturday" Or .Value = "Sunday" Then
Me.Range("B8:J8").Font.ColorIndex = 2
End If
End With
stoppit:
Application.EnableEvents = True
End
End Sub
I also want to change format back if cell changes to M-F.
Excel 97
View 9 Replies
ADVERTISEMENT
Aug 17, 2012
I have an excel sheet that uses date formulas in order to provide data regarding the date that it brings up. My problem is that the formulas do not bring up dates unless the dates are on a weekday. Ex. I would like to look back 3 months in the past, and I do so using the formula =today()-90 (90 for 90 days, about 3 months). I would then like to input this date formula into another function (potentially an if function) so that if today-90 is a saturday or a sunday, the function will automatically bring up the date for the friday preceding it.
View 2 Replies
View Related
Oct 5, 2013
I have three columns named "name","start_date" and "end_date"
ex:
Name start_date end_date
AB 9/11/13 10:19 AM ??
CD 9/12/13 11:45 AM ??
All i need to find is,
If Name = AB, i want put start_date+3 days in the end_date column,
similarly for name = CD , start_date+7 days.
But the calculation should exculde weekends(sat and sun).
ex: For AB, start_date = 10/3/13 (3rd oct , thrusday), end_date should be start_date+3 ,so end_date = 10/6/13 but 6th october is sunday so the formula should give end_date as 10/7/13.
View 4 Replies
View Related
Aug 4, 2014
I have below formula which gives me the due status.
[Code] ......
How can I modify the code so it excludes Saturday and Sunday.
View 7 Replies
View Related
Oct 25, 2013
I'm wondering if there is a way to highlight Saturday and Sunday on my graph: [URL] ......
Here, I have it done with a shape formatted with the transparent light red fill.
View 2 Replies
View Related
Aug 4, 2013
I am trying to automatically get the following series filled in but have failed thus far:
1,2,3,4,5,6,7,11,12,13,14,15,16,17,21,22,23....... (after 7, it skips 3 digits)
Similarly,
Excel has the "Fill Weekdays" option. However, how do I also remove Friday along with Saturday and Sunday from the dates of a year?
View 4 Replies
View Related
May 12, 2013
=IF($C2="","",IF(NETWORKDAYS($A2,$C2+1)
View 9 Replies
View Related
Feb 8, 2012
Can weeknum work with the week starting on saturday instead of sunday or monday?
if not, what are my options?
View 4 Replies
View Related
Jan 16, 2014
I am looking for Macro code preferably to get list of dates with Saturday / Sunday in a separate columns which falls Saturday and Sunday on imputing the year.
View 5 Replies
View Related
Dec 15, 2013
I have attached the xlx sheet. I am a truck driver and I always have to calculate my weekly wage on paper.
I start everyday at different times it varies the end of shift as well. We have day shift, night, saturday, sunday payrates.
I have done an excel sheet I was trying for a couple of days reading forums and watching videos but just I couldn't.
Example if I start Friday at 18:00 and finisth Saturday at 04:00. I have in total 10 hours.
Day shift: 1 hour
Night shift: 5 hour
Saturday: 4 hour
So in one shift I get 3 different payrate. In one shift if I work more than 6 hours it must be deducted 00:45 minutes.
Day shift: 07:00-19:00 - £10.7
Night shift: 19:00-07:00 - £11.7
Saturday - £12.7
Sunday - £14.7
Attached File : DRIVER SHIFTS VARIABLE SHIFTS AND PAYRATES.xlsx‎
View 7 Replies
View Related
Aug 24, 2014
I have a list of dates and I need a formula that will return the date of the following Monday, IF the date falls on a Friday, Saturday or Sunday.
View 3 Replies
View Related
Mar 11, 2014
I am trying to write a macro to send mail on every Friday and also on last day of every month. If the last day of the month falls on Saturday or Sunday then the macro should mail on Friday. I have written a separate macro to send a mail. I have also written to check day(ie Monday, Tuesday etc) of today. If today is Friday or month end i can send mail. I dont know how to tell the macro to send mail on friday if the month last date is saturday or sunday.
Sub done()
Dim Dat As Date, x As Integer, y As Date, sorry As String
Dim str As String
sorry = "Today is not friday or month end. So i cannot send mails"
str = WeekdayName(Weekday(Now()))
[Code] ........
View 1 Replies
View Related
Nov 17, 2012
I am trying to update a spreadsheet for 2012 to 2013 and want to use a formula to change SUNDAY 8 January to Sunday 9 January and so on for rest of year.
View 1 Replies
View Related
Dec 19, 2006
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
View 9 Replies
View Related
Feb 9, 2010
I have five fields that have a conditional format applied to them: (see attached).
Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
View 2 Replies
View Related
Apr 12, 2012
I have a range which has conditional formats based on other cells. I want to copy this range into another sheet and retain the current formats as fixed formats without copying the conditional formulae. ideally in VBA.
View 5 Replies
View Related
Mar 20, 2013
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
View 3 Replies
View Related
Feb 9, 2009
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies
View Related
Jul 7, 2014
Is there a formula to count the number of Saturdays between (and including) dates in A2 and B2
View 3 Replies
View Related
Dec 10, 2013
The attached workbook requires users to enter a date on the SAT sheet (B3). I would like that date to always represent Saturday (the first day of our work week) Is there a formula I can use to validate if the date entered by the user corrleates to Saturday or is there a way to auto-populate the workbook with the Saturday's date?
DSR (BLANK BETA).xlsx‎
View 14 Replies
View Related
Jun 17, 2008
I need to calculate the total WORK-hours (09:00-17:30) between two date/time-stamps;
Including Saturdays but excluding Sundays & Holidays.
I can get this working excluding Saturdays (formula below) using NETWORKDAYS however the call centre work six days a week Mon-Sat.
A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm [code]....
View 9 Replies
View Related
Jun 17, 2008
I need to calculate the total WORK-hours (09:00-17:30) between two
date/time-stamps;
Including Saturdays but excluding Sundays & Holidays.
I can get this working excluding Saturdays (formula below) using NETWORKDAYS however
the call centre work six days a week Mon-Sat.
A1=dd/mm/yyyy/hh:mm
A2=dd/mm/yyyy/hh:mm
DayEnd= 17:30:00
DayStart= 09:00:00
HolidayList= "Currently Blank"
=(NETWORKDAYS(A1,A2,HolidayList)-1)*(DayEnd-DayStart)+MOD(A2,1)-
MOD(A1,1)
View 6 Replies
View Related
Jan 12, 2010
I am given the year (say 2009) in Cell A1.
The requirement is to put the date of last sunday of the year (2009) in cell A2. how to do this?
View 7 Replies
View Related
Apr 22, 2014
how to view changes on my spreadsheet.
I have a tab called SAP DUMP where we copy our raw data to, from here we manually copy and paste into another sheet called ORDER TRACKING SHEET. When we do a fresh dump (overwrite) of raw data into the SAP DUMP tap we need to see if the there is a new entry which needs to be copied across to the ORDER TRACKING SHEET. I've got a vlookup to show this. The one I can't work out is that on the ORDER TRACKING SHEET I also need to see if there are any entries are now no longer on the SAP DUMP tab. This is where I thought a condition format might work, color the line red if it's no longer on the SAP DUMP tap. Can't make it work.....
View 2 Replies
View Related
Dec 16, 2008
I have a workbook with 12 worksheets corresponding to months. Each is formatted in A1 with the month (Date, Jan-01) and all rows below, starting at A5, are the days of the month (Custom, ddd d). All cells are linked so they change accordingly with A1.
I'm trying to apply conditional formatting so that all Fridays will have light grey shading across the row. I can get it to shade every 7th row using the MOD & ROW functions, but the shaded row changes with subsequent months and years (not necessarily Fridays). Can I get it to recognize every Friday through the year.
View 4 Replies
View Related
Apr 24, 2009
Hi1Tasks23-Apr-09Inercona.Trial1
b.Trial2
Tasks23-Apr-09Enercona.Trial1b.Trial2
Disregard the First Table. I cant remove it. refer to the Second Table
This table is a Task Tracker. The Start Button Adds the Current Date in Enercon's Row C1 and the row below (C2) will automaticaly filled with color using Conditional Formating. My Question is how can I code a conditional format for Trail1 Rows. For example when Trail1 Cell is active then you click the start Button instead of Coloring D2 Cell it should Color D3 Cell.
Here is the Code in Start Button
Sub cmdStart_Click()
Dim i As Integer
Results = MsgBox("Are you working on " & ActiveCell.Value & " today ?", vbOKCancel, "")
If Results = vbOK Then
With Sheets("Sheet1")
i = .Cells(2, Columns.Count).End(xlToLeft).Column + 1
.Cells(2, i).Value = Now()
i = i + 1
End With
Else
Range("A1").Select
End If
End Sub
Regards,
Kevin
View 9 Replies
View Related
May 14, 2014
I would like to conditionally format a column of cells such that it formats when a cell's text equals to that in another column.
Example, in column A I have
1 Cat
2 Dog
3 Mouse
...
In B I have
1 Chicken
2 Mouse
3 Monkey
...
58 Cat
Based on the B column I would like to highlight A3 Mouse and A1 Cat.
Additional complexity, In B I have
1 Chicken Mouse Dog
2 Rat Monkey Fish
3 ...
Given that this is the format I have the text in how would I search column A and conditionally format A2 and A3.
View 4 Replies
View Related
Aug 21, 2014
I am having trouble with a conditional format of a date. I need it to turn the date red if it is 35 days older than today.
View 3 Replies
View Related
Sep 9, 2008
I have a conditional format that I wanted to add another argument to, but although the statement is true, conditional format will not toggle.
My original argument was:
View 14 Replies
View Related
May 26, 2009
Maybe I'm getting a blank, maybe the answer is so easy, but lately I can´t solve this: How do I use conditional format to change cell color if cell value (date) is not workday?
View 4 Replies
View Related