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?
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 RelatedI 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 RelatedI 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
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.
I have below formula which gives me the due status.
[Code] ......
How can I modify the code so it excludes Saturday and Sunday.
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.
=IF($C2="","",IF(NETWORKDAYS($A2,$C2+1)
View 9 Replies View RelatedCan weeknum work with the week starting on saturday instead of sunday or monday?
if not, what are my options?
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 RelatedI 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
I am making process TAT(Turn Around Time) which required following information. In Excel 2007.
1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays
2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays
Note : Any weekend (off days) dates listed in holidays should not effect the query.
How to get "only Sunday" highlighted in a series of dates included in excel columns. I tried some formulas found from the web pages, but they all deal with both "Saturday and Sunday"...
View 6 Replies View RelatedI 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] ........
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
I have a spreadsheet, with about 1,500 lines. In column A is a list of numbers that I need to remove the last digit from each number, for example in A22 is 02602726521 - I need this to be 0260272652. The numbers are all uniqe.
How can I remove the last digit from every number, without going in manually to do so?
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 RelatedIs there a way to fill column "B" down to the very last cell located in column "A" when column a varies each time?
View 5 Replies View RelatedI am currently trying to autofill a column or use a formula to try and autofill. I know how to do the dragging thing but that is too time consuming as I have about 30,000 rows and this needs to be done weekly. I know there's a way to do it but I forgot. Basically if you look at the first number below (ends in 0612), I want it to fill all the cells that are underneath it until it reaches the next number (which ends in 0856 below), then I want to fill all the blank cells(that end in 0856) with the 0856 number and continue down.
Is there maybe a formula that I can create in the column next to this and have it do it that way, or is there an option that will let me do this by highlighting the column.
I have a column with blank rows inbetween rows with data.
Column Heading
1000
Blank
Blank
1001
Blank
Blank
Blank
1002
Is it possible to autofill the blank rows with the row that has a value above them, have it skip the next row with a value, recognize the next blank cells and fill them in with the previously skipped row, and so on down the line? How might I go about doing that?
I'm trying to create a code that autofills the next empty row in colum A with a number. What I have at the moment is:
'Create Index in Transaction list
x = Range("A" & Rows.Count).End(xlUp).Value
Sheets("Transaction list").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = x + 1
If I run this code in isolation from the rest of my code (which is copying and pasting values from one sheet to another) it works, but when I run the full code it only autofills the rows with "1".
I am still new to this, and I pulled this code off of another site.
Background:
- Using Excel VBA, 2007
- I have a sheet that has a name in B6, has 4 blank spaces beneath, and then another name, 4 blank spaces, etc.
- I wrote a code to fill each of these 4 blank spaces with the value at the top of each
- The code works, but it won't fill the 4 spaces at the bottom of the column.
What is needed:
-how I can adjust to code below to fill in the 4 spaces beneath the last value in the column?
Code:
Sub FillBlanks()
Dim rRange1 As Range, rRange2 As Range
Dim iReply As Integer
[Code]....
Is there a formula to count the number of Saturdays between (and including) dates in A2 and B2
View 3 Replies View RelatedThe 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
Every other Friday, I need to submit my biweekly work hour sheet to manager. For example, as 12/21/2007 is Friday and pay day, I need to submit my sheet. Next time is two weeks later, 1/4/08, and on.
12/21/07 is the first event.
I want to create a conditional format, or workbook events, or worksheet events to remind me on that Friday when I open that book or activate that sheet.
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
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]....
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)
The following code is supposed to produce six series on an xlXYScatter chart. It produces seven with the seventh series being a repeat of the sixth but named series 7.
Sub Chart2()
Dim DataRange As Range
Dim CellString As String 'Stores a cell range in the form "AA27:AB39"
Dim CurrentSeries As Integer
Dim SeasonCount As Integer
Worksheets("Hemisphere").ChartObjects(2).Activate
CurrentSeries = 1 ............