I need to black holidays out in an Excel workbook so that when a user types in a date range the answer doesnt come back counting holidays in the answer. The formula i have right now counts how many days like Monday there are in a specified date range but i need it to not include any days employees do not work.
View 1 RepliesI would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:
=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")
It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.
I would like to know if it is possible to find the red dates above preferably without a macro.
Check if the below array is empty and then output the date where u have the first non blank value.
I have a workfile, containing dates in column C and values in column G.
The data is imported. In most instances there are no dates in line with the second value. I need VBA code that will insert a the same date in line with the first value below the cell where there is a date....
I'm attempting to write a formula that will allow me to determine the number of days between two dates but to leave the cell with a zero in it if either of the first two date cells are blank or have N/A in them. Here's what I have so far:
=IF(AND(K5="N/A",L5="N/A"),0,L5-K5)
This will give me a zero in the cell if both date cells are blank or will give me the days between if not. How do I get it to also put a zero in the cell if only one of the date cells is blank?
How can i leave Cell D3 etc blank untill i have inserted the end date it will look tidier when i do my full spread sheet and also is there a calender on a toolbar i could put on my spread sheet for easy inputing of dates.
View 5 Replies View RelatedI need to insert blank row/s between the missing dates. Let me explain in detail,
I have a start date in Cell B1 and end date in Cell D1. Need a macro to verify the date range in Col B with these start & end dates and insert blank row for missing date corresponding to employee names in Col A. This should be repeat till last used row of the sheet.
Attached sample (Before&After) workbook.
Insert Missing Date.xlsx‎
I am indexing dates from one workbook to another.
There are some blanks in the list that I am using, and I need to the blanks to stay as blanks in the sheet that I am indexing to, however they are displayng as 00/01/1900.
I have a spreadsheet that contains dates in column A, the number of rows between each date can vary. Selecting a cell with a date in will activate a checklist in the form of a UserForm.
I am trying to create a macro that will count the number of rows from one cell with a date, to the next cell with a date, and then resize the selection for printing.
So far I have this;
Code:
Sub test()
Application.ScreenUpdating = False
Set InitialCell = ActiveCell
[Code]....
My problem with this code is that when it reselects the InitialCell, the UserForm is reactivated. Is there a way to achieve the same results without having to reselect the InitialCell, and therefore the UserForm wont pop up? I tried adding the Unload UserForm1 line but it doesnt have any affect, the form still pops up.
How can I change the ....
View 14 Replies View RelatedI have the following formula which counts number of days between dates in two adjacent cells excluding holidays
=NETWORKDAYS(U311,V311,$AA$4:$AA$14)-1
The $AA$4:$AA$14 refers tote range where the excluded holiday dates are stored.
I have included the -1 at the end as it over counts by one day every time. i.e. if the same date is in both cells it counts 1
However if no date is in both fields it count -1 (minus one)
If a date is just in one field it counts a very high number, example below.
-1
16/07/2014
-29879
20/06/2014
08/07/2014
12
[code].....
is there a better formula to use for this? How do I get the result to show ZERO when both cells are empty? can I get the result to show number of days to present date where there is only a date in first column?
I have a formula '= COUNTIF(A1:A5,"<"&TODAY())' that counts the number of expired dates in one column. I want to include a second condition to this formula that will check if another column of dates are blank then they should not be counted. So if for example a1:a2 have expired dates, and b1:b2 have both got dates then they should be included in the count.
View 9 Replies View RelatedI have been using CountIfs with a lot of luck until I added one extra criteria that it ignores.
ORIGINAL FORMULA
=COUNTIFS(ADate, ">" & N$2, ADate, "=" & 10000, WBid, "" & N$2, ADate, "=" & 10000, WBid, "
I have a workbook with multiple worksheets. Most have data. 2 have been hidden or something. The tabs are there but there is nothin on them. No grid lines, not a blank worksheet. It is blue. What does this mean? How do you do that? How do I un-hide to find if there is data there...etc.
Like I said, the tab is showing amoung all the other tabs, but there is nothing - no worksheet - just blue.
I currently have a macro that will work if there is a workbook open. However I would like to change it to, once the macro is sected from toolbar, it opens a blank workbook and then does what has been set up. What line(s) of code would I use to do this at the start?
View 5 Replies View RelatedI am working with two files everyday: today's and yesterday's. Each report has the day's date in the name of the file.
So I'll take today's (11-5) and import it into yesterday's (11-4). Then, tomorrow I'll take 11-6 and import into 11-5. The day after, I'll take 11-7 and import into 11-6. So on and so forth. I'm trying to figure out how I can get VBA to accommodate these changes in names. So that it sees the date in the name of today's report, sees that it is today's, then selects yesterday's (or says "Open yesterday's report!"), and imports the information.
I can figure out everything else, just not the naming issue.
I guess I could always stipulate to the person running it that they should only have these two workbooks open and that they should run the macro starting in yesterday's workbook. Just writing VBA code to select the only other workbook so VLOOKUP can be done from it. But I am trying to make it fool proof.
Running excel 2003 and would like to make a macro for a workbook. Example I want a macro to say (see the big truck), I can record this ok but can't get it too play in any cell on my workbook. I want too be able and input this in any blank cell on the workbook. Is this possible? when I try this I just get nothing in the cell.
View 2 Replies View RelatedWhen I open my workbook, I'd like to go at the first empty row of the worksheet.
View 14 Replies View RelatedWhen I open a saved excel file I am having a problem with one workbook opening(a blank one), then the box saying that the program contains macros, and then the second actual workbook opening. I do not know what I changed somewhere alone the line, how to make just the one workbook I want (the second one) to open?
View 2 Replies View RelatedWhen I open any saved Excel file the saved file opens along with a blank workbook titled "Book 1". How can I make it so when I open any saved Excel file it only opens the file I want and not a new workbook every time?
View 2 Replies View RelatedI have been searching for a macro that would lock all the non blank cells (cells that have any value/text) after the workbook is saved.
When the user clicks on save a message box should prompt stating that "The data you entered has been locked successfully".
I'm trying to pull out the earliest and latest dates associated with a text string in a workbook.
Min Max dates example.xlsx
i.e. in this example, I want one column to display the earliest date associated with DU 145, and the next column to display the latest.
From what I understand, I need an array formula to do this and have written the following, which works:
=MIN(IF($A$2:$A$6=A2,$B$2:$B$6))
(confirmed with CSE) - and the same for MAX.
Where I'm struggling is that DU 145 may be entered in the workbook as DU145, DU 145 or DU-145 and I need to take all of them into account. I tried:
=MIN(IF($A$2:$A$6="du*145",$B$2:$B$6))
As this works in a COUNTIF formula in the same sheet. But this just returns a date of 00/01/1900. I'm new to array formulae & haven't really worked them out yet. Is there a way to do this? I assume it's something to do with the way I'm entering text as removing the wildcard * makes no difference.
1- 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 am trying to educate myself in the wonders of Excel (2003) and an struggling somewhat.I have a small project loosely based on a shops stock control and have attached a copy of the workbook -
The user normally only has access to columns B, C and E due to the others being locked and the sheet protected.I would like the sheet to be automatically sorted based on column E each time the workbook is opened.I then need to automatically delete a row once an item in column B has been removed (made blank)
I have spreadsheet of data, I need to extract any rows that have blanks cells in columns F or P or T.
If possible I would like a macro I could run that would cut all of the rows that meet the above criteria and paste them in to a separate sheet.
Anyone know of a way to detect if a cell is blank in a closed workbook?
Here's what I'm trying to do:
=IF(ISBLANK(closed workbook cell reference),NA(),closed workbook cell reference)
also tried:
=IF(closed workbook cell reference="",NA(),closed workbook cell reference)
Neither one worked. I'm using Excel 2007.
I have a worksheet with some info that is filled in from other employees. I have Customer Name, Date of Oldest Invoice, Avg. Days Beyond terms in column N42:N44, with the data in column M42:44. I want to copy and paste that into another workbook, I need to transpose the data and then paste it into the next blank row. IF POSSIBLE, I would like to have some of the column headings the same as the above fields, and I would like to paste the data into the correct column heading.
Currently I have the code listed below. It copies the data, opens the spreadsheet, (this is where it fails) find the next blank row and transpose and paste the data. It does transpose and paste the data but it does not look for the next blank row, instead it just transposes and pastes the data in the last cell that was active when the work book was closed.
Code:
Private Sub CommandButton1_Click()' Paste Macro
' Macro recorded 5/14/14
'
'
Range("O42:O47").Select
Selection.Copy
Workbooks.Open Filename:="L:Financial Services! FRA !! Preston !3rd Party Collections Accounts 2014.xls"
[Code] ..........
I have Excel 2002. If I start the program it opens with a blank workbook, called "Book 1".
If I then open an existing workbook, I have two Excel buttons in the Windows taskbar: the one I just opened, and the blank one titled Book 1.
At work I have Excel 2003. The blank "Book 1" is also there when I start the program, but if I then open an existing workbook, the Book 1 goes away. I like this way better.
My question is: Is this just the way versions 2002 vs. 2003 work, or can I change a setting somewhere to make Excel 2002 close its default blank workbook when I open an existing one?
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
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 Related