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

Date Automatically Changes If Saturday Or Sunday?

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

How To Exclude Saturday And Sunday In Date Calculation

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

Formula To Exclude Weekends (Saturday And Sunday)

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

Graphs - Highlighting Weekends (Saturday And Sunday)

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

Autofilling Series - Remove Friday Along With Saturday And Sunday

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

Formula / Adding 2 Days To Saturday And Sunday For Count?

May 12, 2013

=IF($C2="","",IF(NETWORKDAYS($A2,$C2+1)

View 9 Replies View Related

Week Starting On Saturday Instead Of Sunday Or Monday? (formula Not Macro)

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

Macro Code To Get Saturday And Sunday Dates On Inputting Year

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

Lorry Driver Shifts (Day / Night / Saturday / Sunday) Variable Payrate

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

Move Date To Next Monday If Date Is On Friday / Saturday Or Sunday

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

Send Mail On Last Day Of Every Month If Last Day Is Saturday / Sunday Send Mail On Thursday

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

Convert Sunday 8 January To Sunday 9 January Using Formula

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

Save Conditional Format Colour But Delete Condition Format

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

Conditional Format Based On A Conditional Format

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

Copy And Paste Actual Format From Conditional Format?

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

Conditional Formatting Based On Another Cell Conditional Format

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

Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?

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

Formula To Count Number Of Saturday?

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

Auto-Populate Workbook With Saturday Date?

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

Calculate Working Hours Between 2 Dates Including Saturday?

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

Calculate Working Hours Between 2 Dates Including Saturday

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

Last Sunday Of The Year

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

Conditional Format Or Another Way?

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

Conditional Format According To Day

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

Conditional Format In VBA

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

Search And Conditional Format?

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

Conditional Format For A Date?

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

Conditional Format Not Working

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

Conditional Format If Not Workday

May 26, 2009

Maybe I'm getting a blank, maybe the answer is so easy, but lately I cant solve this: How do I use conditional format to change cell color if cell value (date) is not workday?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved