I need a macro which will give me the number of lines between two search dates.
The dates are in the format DD/MM/YY e.g. 01/02/08
The dates are in Column F starting from F8 down the bottom ( which is always being added to).
I either need the macro to ask for the two serch dates, or I could just enter the two search dates in say cells A1 and A2
The serch could just be a "text" search e.g. look for string 01/06/08 in Column F make a note of the line number, keep looking down Column F for say 31/11/08, when found count the number of lines between the two.
Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.
What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc
I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.
im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
I have a sheet named "XYZ Activity" with meeting dates in column B starting with cell B4. There are duplicates in this list, that should only be viewed as one meeting. On another sheet, each company that attends the meeting has a specific join date found in Column C (C4 is the first company start date). I am trying to figure out an equation that will count how many meetings the company could have attended. I already calculate the total meetings they have attended using either of the following equations:
The attached workbook has dates in column C, although some of these dates are just strings.
I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.
At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.
not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached): ...
I then have another table and need to total the pallet quantity by month. Does anyone know a formula where i can have a TOTAL pallet figure by month, therefore showing October as having 53 pallets?
I track dates of training for my fire department. Training is due every two years I want to know when the date is more than two years old and when it will be two years old in the next six months or less. I have conditional formatting that changes the color of a cell based on two conditions. Condition #1 (Yellow) the date is more than 2 years old, [=TODAY()-B2>730] Condition #2 (Green) the date will be more than 1 year old in less than half a year. [=B2+182-TODAY()<185]. Is it possible to count each occurrence those two conditions with a formula?
I'd like to do this without having to write a macro and am looking at a possible worksheet function (or combination of functions) that someone could reccomend to get this done.
- I have a tab labelled "Data", in this tab there is a column (lets say C) with dates (in mm/dd/yy format)
- Then on another tab ("Summary") I have a table with each row of column A being the first day of every month (same format).
I want a count of all the dates in "Data" column C that fall between the date on "Summary" A1 and "Summary" A2 and I want to dump this in "Summary" B1.
I've tried COUNTIF, I've tried converting the date to a number, not sure what else to do or if it's even possible.
i want to could count the cells of F13:F50 only if the dates of B13:50 are between the two dates i have entered in C1 and E1. I have completed one section of this which counts the dates of B13:50 if they are between the two entered in C1 and E1: =SUMPRODUCT(('1'!B13:B211>=C1)*('1'!B13:B211<=E1)) i just cant get it to work with the second set.
I am a remodeling contractor trying to monitor trips my employees are making to the lumberyard. I am able to export to excel from our accounting program a column of dates that invoices are made and another column that tells me whether trips were made before 8:30, between 8:30 and 3:30, after 3:30, or whether materials were delivered involving no trip. So I can count "time of day" trips. I also want to know if multiple trips were made in a single day, or if trips were made 2, 3, 4, 5, etc. days in a row.
Column 1 Column 2 Monday, July 6 2009 Before 8:30 Monday, July 6 2009 Between 8:30 and 3:30 Tuesday, July 7 2009 Between 8:30 and 3:30 Wednesday, July 8 2009 Before 8:30 Friday, July 10 2009 After 3:30 Monday, July 13 2009 Before 8:30 Wednesday, July 15, 2009 Before 8:30
In this example, I have 2 trips made on a single day; 1 instance of trips made 3 days in a row; 1 instance of trips made 2 days in a row (Friday, and then again on Monday since we don't work weekends) and 1 instance of 1 trip made on an isolated day. Additionally, we have 4 trips made before 8:30; 2 trips between 8:30 and 3:30 and 1 trip made after 3:30. Columns for a single job could range to 30 or 40 trips or more, and we have 6-12 jobs running at any one time, so I could be looking at data for all of those jobs once a month if I can figure out how to make it easy to do. I have figured out how to count up the time of day trips (but included the example here for a fuller picture of what I'm trying to do) but cannot figure out how to count the number of 2,3,4,5 etc.-day-in-a-row trips that are being made.
I wish to calculate the days between the starting date (column A) and ending date (column B). For the first 7 days are excluding all the holiday and weekend and the rest of it until the ending date are counted.
I am trying to count the number of dates in a column that are within the last 180 days. I tried using COUNTIF and it did not work. Formula which I thought should work: =COUNTIF(A3:A32,>(A40)). My workaround is shown in Column B, but this method adds one column for each participant.
I found these 2 examples online on how to count Mondays between 2 dates but neither seems to work on Excel 2003, which I am using. Can anyone help me please?
Number Of Mondays In Period
If you need to return the number of Mondays (or any other day) that occur within an interval between two dates, use the following Array Formula:
A2 contains the beginning date of the interval B2 contains the ending date of the interval C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday) ....
For each of my staff I need to count if they worked 15 or more days in the 30 days preceding a statutory holiday. (As a follow on problem... If they have worked 15+ shifts then I'll need to calculate the sum of their hours over the 30 days / 30).
I tried the functions below but they didn't work. (the Name1 column records the number of hours worked that day's shift) The actual count in my test case is 5.
I have a problem again with one of Date Dif function " =DATEDIF(I5|J5|"m") " in attached file i have this function applied to calculate number of months in between two given dates but it seems this function is not working properly or i am missing some info in this to make it run properly. How to fix this function or give me a proper one which can solve my problem to calculate accurate months between these dates in attached file...
Worksheet is an action log. It has a column containing "due dates". I want to count all rows (dates) that are "less than" today (to see what work is overdue). I also want to count all rows (dates) that will come due in a week. Conditional formatting highlights them OK but I also want to maintain a set of counts. I can do all this in a macro but I don't want to use macros, just formula.
I have a range of data in a spreadsheet that has entry dates. To one side I have a cell that =today() What I want to do is where the entry date is in my range of data is insert a column that will tell me "yes" if the entry date =today()-1
