I have class monday, tuesday, wednesday & friday but no class thursday. How can I make automatically fill in dates for weekdays only and skip thursday using excel 2013's "Fill" function then "series" ??
View 6 Repliesi had a problem to fill in weekdays of 2014 excluding friday and saturday as holidays.
View 2 Replies View RelatedI would like to be able to auto fill weekdays (skipping Saturday & Sunday) in a month. For example, my spreadsheet would look like this if cell A1 contains12/01/09:
DATE WORKDAYS TASK
1-Dec 1 AP CLOSE - CAPITAL & POS ITEMS
1-Dec 1 POS MONTH END
1-Dec 1 HEALTH INSURANCE
2-Dec 2 DEPR SYSTEM RUN; INCL-SOFTWARE AMORT
2-Dec 2 SPECIAL EQUIPMENT
4-Dec 4 PRELIMINARY ENERGY REPORT
7-Dec 5 AP CLOSE - ALL OTHER ITEMS
The DATE column needs to return the date for WORKDAYS listed...Monday through Friday only. The first workday in Dec 2009 is 12/1, the fifth workday is 12/7, and so on.
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedI was going through "To do list with progress tracker" template in Excel 2013. I noticed when i scroll down the page the heading column (A,B,C,D,E,F,G etc.) automatically matches with the inserted table headings.
View 1 Replies View RelatedI have a column of dates in Col B that I need to update every day. I want a macro that will automatically fill down the date in the column, but it has to be a working day. So far I have this:
Code:
Range("B3").End(xlDown).Select
Selection.AutoFill Destination:=Range("B3:B" & lastrow), Type:=xlFillWeekdays
However this doesn't work. Obviously the range will need to be dynamic (i.e. it will change from day to day as new dates are added).
I have a Table ("Table2") in a worksheet ("Dashboard") that contains monthly data arranged in rows. I have made the data fields show #NA for months I want to exclude based on dynamic criteria.
Now, I would like to automatically filter out the rows (months) with #NA so that they are not charted.
It works manually but I have to Right Click on the Table --> Filter --> Reapply every time the source data changes. How can I make this happen automatically so that the user does not have to manually reapply each time.
If you look at the file I attached you will see the tabs at the bottom are months of the year. I know how copy a sheet but every time I make a new month tab I have to retype the month in the top left corner of the table, in the tab, and other places. This gets really repetitive when I have to do this for literally around 20 other spreadsheets like this.
What I'd like is a way to create new month tabs that automatically rename the month and year in chronological order on the sheet and tab if possible. 2 months it's because I'm constantly having to tweak spreadsheets for my boss. It'd be pointless to go ahead and make a template because then I'd have to go back and change the code for every month. So I just create tabs and edit code month by month.
I am making a holiday form with names down the page and dates along the top, what I am trying to do is create a user form so it will automatically fill the dates to which they are on holiday. What I would like the code to say to find the cell at a specific date (column) and person (row) so it will enter a custom text or number.
The data that is submitted on the user form would be a start date and end date and the person’s name.
I have Excel 2013.
Find attached a sample fileDummy for area between.
charts.xlsx
I imported a text file into excel 2013, and the format for the date column was M/D/Y. Like this:
Excel Forum 1.xlsx
And then I copied those date and pasted them onto this Excel file:
Excel Forum 2.xlsb
And as you see there seems to be a problem with the dates. So I formatted the cells to also have the M/D/Y format and this is what happened.
As you see the original dates are transformed to something completely different.
I have a list of dates and want to convert them to something like Monday 21st October instead of 21/10/13.
I am sure this is easy but cannot see how to do it. I am on Excel 13.
We have 3 PCs, all running MS Office 2013. On 1 of these machines, it is doing strange things with formatting. If you open a document or try to paste anything into certain documents, it decides everything is currency format and assigns all sorts of wrong formatting to the entire sheet, or the entire document. There may be some cells in the doc that are indeed currency, but only a small proprtion. If I open a new, fresh document and paste into that document, it does not do this, it seems to work normally, only applying currency formatting where it might be applicable. On some larger docs that have this issue, no matter what I do, it just continues to apply these strange settings.
View 2 Replies View RelatedI am using Excel 2013. I have a spreadsheet that has a lot of very tall cells. When I use the scrollbar to scroll to certain parts of the spreadsheet it will automatically scroll up or down to the top of a cell. So if I want to view the middle of two tall cells that take up the whole screen it will automatically scroll up to the top of the highest cell that I'm currently viewing.
I just want to be able to scroll to where I want to scroll without Excel moving me up or down. I've tried to find answers on message boards but no luck.
I need to run job between 4 July 2012 and 1 September 2012 but only Monday through Friday.
I have used weekdays() to find the name of dates between these two dates but need to narrow to Monday through Fridays.
I am currently assigning dates to a list manually by giving 5 dates on Mondays to Fridays. I currenly need to do this for around 500 rows and as you can imagine this takes some time.
Is there a way to have a formula that would assign for instance 5 dates on each weekday, purley by dragging the formula down?
For example....
ID
Date
a
07/30/12
b
07/30/12
[Code] ......
I know there have been posts regarding this before, but I can't quite get any of them to work. I'm trying to create a function which counts the number of working days between two dates. I've come up with the following code, but it doesn't work.
Function dayscount(Firstdate As Date, Lastdate As Date)
Dim x As Long
x = 0
For i = Firstdate To Lastdate
If WorksheetFunction.Weekday(i, 1) > 1 And WorksheetFunction.Weekday(i, 1) < 7 Then
x = x + 1
End If
Next i
daycount = x
End Function
I then use the formula "dayscount(A1,B1)", where A1 is my first date, and B1 is my last date.
I use a system which produces excel spreadsheets similar to this example (although much larger than this!). This makes it extremely difficult to carry out sorts and to tidy up the spreadsheet - generally I have to do this manually. I am hoping that there is a way that I can easily add in the data as per document 2 in red.
View 2 Replies View RelatedHow to fill a web form automatically with excel data. I need to sign up like 300 people on a site per day. Their detail are given to me in excel. How can I automatically fill the form with the excel data without spending time doing copy and paste for each person's data?
View 1 Replies View RelatedI am looking to use a VBA routine so when a Date is added in a cell and a number is in the cell below on running the routine I would like the Date to be filled by serial number to the cells to the right. So if D3 has a date in the cell say 03 July 2014 and E3 has the number 10 then to the right of D3 it fills the Date's 10 Cells to the right 03 July 2014 through to 13 July 2014.
Code:
Dates
03/07/2014
Amount
10
Attached is a sample worksheet. I have data in column A that I want to drag fill in column C. In column C, once the first 2 results in column A are entered, I would like a blank cell and so on.
testdv.xlsx
I have the following formulae in seperate cells were A1, A2 and A3 are the cells
A1 =MAX(Data!C2:C3)
A2 =MAX(Data!C4:C5)
A3 =MAX(Data!C6:C7)
I would like to drag the cells so that automatically the cells below A3 are updated as follows
A4 =MAX(Data!C8:C9)
A5 =MAX(Data!C10:C11)
etc up to 600 rows
Unfortunately it is not being updated in that order. Do you have any suggestions how it can be done.
I have two columns with total hours worked at different places and a grand total for both together in a third column.
I'm trying to sum the total hours for the week so far from Thursday till the following Wednesday for each week.
I want the wookbook to look up todays date and tell me what the total hours are for the current week
I'm using Excel 2003 - see the file attached
I have an excel file that contains a list of all clients and want to add an "Active Client" tab within the workbook. The list indicates some active, some inactive. The "Active client" column shows an x at 1st row or if "Inactive" is left blank. I want to auto populate all "Active Clients" to a different Worksheet without having blank rows or FALSE as the result of the current formula ...
=IF('Lead Data'!B17="√",'Lead Data'!H17)
I have a workbook that contains a macro that when executed creates a copy (duplicate) of one its sheets and places it in itself (the same workbook). The macro then prints the newly created sheet. After printing, the macro then erases the newly created sheet so that it is no longer a part of the workbook. My problem is that I get the Excel message "Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete". I would like add to my macro so that 1) the message is skipped or 2) the macro answers the message box (with Delete) so that no user input is required. I would like it if the user did not have to respond to this message.
View 2 Replies View Related1- I dont know why but sometimes excel 2010 converts numbers to dates automatically in my workbook. I use this macro to solve the problem, but since last week it is not working. I get an error: "Method 'NumberFormat' of object 'Range' failed".
2- because it should work in all cells of the workbook, the macro must make changes only in the cells with absolute numbers (ex: 1223) but must not change if in the cell there is a date like 12-may-2012 (numbers with hyphens).
This is my function:
_____________________________
Sub PuxaDatas()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
[Code].....
I need to figure a way to make to cells with dates equal each other if the
day,month and year are the same but are placed into a cell at different times during the day. "Making Date Now () = (06/29/09) In another cell". Therefore, A1= Now() and E11 = 06/29/09
I am trying to compile a count of dates using a numerical reference which adds x consecutive dates onto the initial start date based on the number in a cell.The dates added cannot fall on a weekend.
The start date for each calculation begins in D4 with the corresponding number of days to add onto this date in K4. In the example below 8/11/2007 -- 2 means that the 8th and 9th of Nov get one added to their count. As the next row also has 9th Nov as a start date one more is added to 9th Nov. As the 10th and 11th were a weekend they are skipped and the count starts again from the next Monday. I have enclosed a simplified worksheet with some sample data.
Example:
D4 --------- K4
8/11/2007 -- 2
9/11/2007 -- 3
Expected output:
AE4---------AF4
8/11/2007 -- 1
9/11/2007 -- 2
12/11/2007 --1
13/11/2007 --1
I tried to find this answer but maybe this will be easier to explain. What can I add to this code to make each fill to the right?
HTML Code:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
[Code] .....
In the first part E1 is pasted to A1, if A1 is filled then paste to B1, then to C1
For this example.. Think of this information:
shape = rectangle 1
starting fill colour = 255,255,255 (white)
I have a cell on the other part of sheet which has a value of 0 - but changes to 1 when I hover over another part of the sheet.
I need this shape to change fill from white to an image whenever that cell changes value to "1".
I need all this without clicking on an object! JUST when the cell changes, I can mange it with a click but that's besides the point.