Week Starting On Saturday Instead Of Sunday Or Monday? (formula Not Macro)
Feb 8, 2012Can weeknum work with the week starting on saturday instead of sunday or monday?
if not, what are my options?
ADVERTISEMENT
Move Date To Next Monday If Date Is On Friday / Saturday Or Sunday
Aug 24, 2014I 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 RelatedFormula To Exclude Weekends (Saturday And Sunday)
Aug 4, 2014I have below formula which gives me the due status.
[Code] ......
How can I modify the code so it excludes Saturday and Sunday.
Formula / Adding 2 Days To Saturday And Sunday For Count?
May 12, 2013=IF($C2="","",IF(NETWORKDAYS($A2,$C2+1)
View 9 Replies View RelatedMacro Code To Get Saturday And Sunday Dates On Inputting Year
Jan 16, 2014I 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 RelatedDate Automatically Changes If Saturday Or Sunday?
Aug 17, 2012I 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 RelatedConditional Format If Saturday Or Sunday
Oct 17, 2007I 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
How To Exclude Saturday And Sunday In Date Calculation
Oct 5, 2013I 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.
Graphs - Highlighting Weekends (Saturday And Sunday)
Oct 25, 2013I'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.
Autofilling Series - Remove Friday Along With Saturday And Sunday
Aug 4, 2013I 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?
Formula To Obtain Current Week Monday Date
Mar 17, 2013The portfolio team uses data that is refreshed every Monday. Write a formula to obtain the current week's Monday's date.
View 3 Replies View RelatedAdd Weekly Values From Monday To Sunday?
Jul 30, 2014The required results from the below data are:
Required Result
Week 25 = Batches 25
Week 26 = Batches 40
Data
Data - Day
No. of Batches
16/06/14 Mon
5
17/06/14 Tue
6
18/06/14 Wed
9
[Code] ..........
Dynamic Weekly Graph To Run From Monday To Sunday?
Oct 3, 2011Is there a way to create a dynamic chart to automatically run from the most recent Monday to Sunday. So on Monday the entire chart will reset?
View 9 Replies View RelatedLorry Driver Shifts (Day / Night / Saturday / Sunday) Variable Payrate
Dec 15, 2013I 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
Count Full Weeks Monday Through Sunday Between 2 Dates
Jul 16, 2014Another thread that wanted to count "weekends" got me to thinking about this one:
Count the full weeks (from Monday thru Sunday) between 2 dates.
For example...
Data Range
A
B
C
D
E
F
1
Date
Weekday
------
Start
End
Weeks
2
8/1/2013
Thu
8/1/2013
8/15/2013
1
3
8/2/2013
Fri
[Code] .........
Full Monday thru Sunday weeks = 1
This array formula** entered in F2:
=(MAX(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=7,ROW(INDIRECT(D2&":"&E2))))-MIN(IF(WEEKDAY(ROW(INDIRECT(D2&":"&E2)),2)=1,ROW(INDIRECT(D2&":"&E2))))+1)/7
** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key then hit ENTER.
The logic of the formula is:
The max Sunday date within the date range minus the min Monday date within the date range +1 = total days divided by 7 (days in a full week) = full weeks
How To Set Monday As First Day Of Week
Jan 26, 2014Outlook allows you to easily specify, but eExcel always treats Sunday as the first day of the week by default. Yes you can manipulate that within formulas and functions, but if you use the autofilter in excel and choose "this week" (or "next week", or "last week") excel considers Sunday as the first day of the week. I would like to know how (or if) this can be changed to Monday as the first day of the week.
View 5 Replies View RelatedReturn Monday Of Given Week
Feb 16, 2007I have seen this before and once figured it out, but I have not had any luck this time. I'm looking for a formula that will reference a cell that contains a date and will then return the date of the Monday of the given date's week. If I gave it a cell that contained 2/15/07 it would return 2/12/07. Probably a simple question but I think I must not be working on all cylinders. =)
View 6 Replies View RelatedSend Mail On Last Day Of Every Month If Last Day Is Saturday / Sunday Send Mail On Thursday
Mar 11, 2014I 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] ........
Convert Sunday 8 January To Sunday 9 January Using Formula
Nov 17, 2012I 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 Related6 Months Ahead If Falls On Monday If Not Next Monday Date
Sep 23, 2007I am currently using this formula =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) to get me 6 months ahead from a certain date. There is a new problem that has arose for me. I need a formula that will give me 6 months from a certain date if it falls on a Monday if the 6 months does not fall on a Monday then give me the next Monday date.
for example 6 months from 1/2/2007 is 7/2/2007 which falls on a Monday which is fine but, If the start date was 1/3/2007 this falls on a Tuesday so I would need the next Monday date 7/9/2007.
Formula To Count Number Of Saturday?
Jul 7, 2014Is there a formula to count the number of Saturdays between (and including) dates in A2 and B2
View 3 Replies View RelatedShow Week Number Of Today Date From Starting Date
Jun 27, 2014is it possible to display the week number of todays date (today()) from a physically entered start date (which would obviously be week one), the start date would be november 4th 2013.
View 3 Replies View RelatedAuto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets
Jan 19, 2010I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
Auto-updating Formula For Week Over Week Change?
Dec 11, 2013I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
Formula For Date Of Last Monday Of The Month
Feb 2, 2004This formula on the forum has helped me to find the first day I specify of the month of a certain year. (here a monday in august of the year specified in B1)
=DATE(B1;8;8-WEEKDAY(DATE(B1;8;1);3)-IF(WEEKDAY(DATE(B1;8;1);3)=0;7;0))
As I'm not really good at understanding the formula (I get confused with the =0;7;0 bit), even though I can make it work I'd like to know how I can find the a last specific day (mo-su) in a month.
Auto Insert Row Before Monday With Formula And Formatting
Jun 21, 2014In column H I have a list of dates as I will add new rows each day for different records - it's a payment collection. e.g.
18/06/14
18/06/14
19/06/14
19/06/14
19/06/14
21/06/14
23/06/14
21/06/14 is Saturday & 23/06/14 is Monday, Sunday is holiday so that day is no collection but I want Sunday row also be there with date in that column. Currently I am doing it manually.
Is there a method with VBA (or other) to automatically insert a row with formulas & formatting above Monday date after workbook save.
I have data validations, conditional formatting & lot of formulas there. I have other date column also on workbook but I want it happens only with column with H. Also what if I have to change column H in future.
Popup Macro, New To Vba, Due Monday Night
Aug 17, 2008What I was told to do was to create an Excel Macro that would record 7 fields via pop-up box. I had no problem with this (see code below) but now I would like to run that same macro for 10 or more students. I would like to have the same data collected (first name, last, email, address, phone, etc) for each student, but have excel compile a list of the recorded data for each individual. Ideally John, Mary, Will, Greg, and Matt would fill out the form and their responses would appear as:
First Last Email Phone Other
John Doe johndoe@yahoo.com 5551245 etc etc
Mary Jones mjones@gmail.com 5551227 etc etc
Next Student student@google.com 5551212 etc etc
The macro is started by clicking on a button to register for a free giveaway
Originally Posted by code
Sub RetisterToWin()
Dim FirstName As String
Dim LastName As String
Dim Email As String
Dim MailingAddress As String
Dim PhoneNumber As String
Dim Major As String.............
Sumproduct (get The Number Of People "Occupied", "Partly Occupied" And "Available" On A Monday, Tuesday Etc Of The Current Week)
Oct 22, 2009I'm having issues with sumproduct. I can't seem to get the right info that I need. Attached is the file I'm working on. Problem: I need to get the number of people "Occupied", "Partly Occupied" and "Available" on a Monday, Tuesday etc of the current week. "Occupied" means an employee has more than 2 tasks (based on New and Active-To-Date status). "Partly Occupied" means an employee is working on 2 tasks.
"Available" means an employee has NO task at all.
Formula To Tell When Its Week 1 Or Week 2 Of A Any Given Month
Jun 16, 2014I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.
I'm using weekday formula but no luck.
Macro To Highlight Cell A1 Every Monday Only Once When Open Book For First Time
May 20, 2013Macro to highlight a cell A1 every monday ( or any day) when we open the book for the first time.
Macro should not run when we open the book from the next time till that week gets over and again it should run once the next week started.