Using Count Function With Dates In Ddmmyy Hhmm Format
Sep 3, 2009
I have an order tracking sheet that has start times in column B, end times in column C and the processing time in column A. The start and end times are recorded in ddmmyy hhmm format (9/1/09 13:30).
On another sheet I have dates listed for each workday. Next to these dates I am wanting a formula/macro that will traverse through column B and provide the number of orders and average processing time that match this. I also plan on recording the shortest and longest processing time next to these.
I need a formula that can count items that are past due by up to 30 days, 60, or 90 days. I'd guess the total of all 3 values should = the total past due.
I have the total past due as =COUNTIF(A!I$1:I$278, "<=" & TODAY() )
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 need to retrieve 6 digits (It's really the date in "ddmmyy" format) from a string. If the strings were to follow a standardized format it would be a simple matter of using left, right or mid. However, I have a couple of different scenarios.
The "ddmmyy" I'm looking for may be sandwiched between other characters, separated by a space or remain at the end of the string. Is there a way to sniff out the 6 digits in the string regardless of it's position?
Here are some examples:
1) ABC COMPANY 3.6% 100111-BLAH 2) MCCAIN JOHN4.9%120509 3) ROYAL BK SCOTLAND 4.03% 160309 4) IBRD 8% 090210 BNPHFN00002 HKD5) AUST & NZ BANKING 4.23%161109-EMTN
I have a template in which i resave everyday to ddmmyy, i then remove the following sheets "raw_data","Exchange","Advanced Filter", i also remove all the formulas in my other sheets to values. I would like to have a macro that will do all of this for me.
the file is saved in,
C:RecstatsOver2day2009Nov
but would like the year and and month to be generic and not hardcoded to say 2009 or Nov.
Hi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.
I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.
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 some dates in a format which are not being converted into the normal date format. So i made this formula to convert it into the normal date format:
Formula is working fine except on this type of date "9/9/2013"..i also tried an OR formula with mid but didnt get the desired result.These are the type of dates:
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 have a forecast which is sorted by product code by week vertically. I need to create a lookup to re-format this information into a horizontal format by week. I have started this by transposing the information as my attachment shows but I am hoping there is a lookup formula which will be easier and quicker. I have attached the actual document and the data I want to sort is Sheet 2. I have started in Sheet 1. I want to look up the code in column A, then lookup the week number which would be B2 in Sheet 1 and return the value of that Code in that week from Sheet 1.
I want to count from each cell that doesn't contain "0". So if cell C2=100, I want to be able to count the number g1*2 from that cell and return a value. But then I want to start another count from c5 to the number of g1*2 and then another count from c8 etc basically any cell that contains a value other than "0", I want to start a count from.
The point of this is that the half life will expire after that count, so I want to be able to add the drug levels on an ongoing basis until the count of the half life has been reached. But there will be further dosing along the way before this half life is reached and these values need to be added to the existing value until the half life expires.
And I want all dates that is saturday and sundays marked in grey. If its possible I want the whole D column marked grey (if D1 is a saturday or sunday), is it possible?
I have a file from a database that includes birthdays and anniversaries. These are mainly in US format as far as I can tell, but I am trying to get these all in UK date format but when I try and change any of the formatting half of them don't seem to change.
I have used excel for years. I understand how to format dates. However, lately, after I tell the cell to format it as a date, and then type in a date, it inserts some random date (not the numbers at all that I entered).
I am trying to move the data from sheet1 column A to sheet2 column A while reformatting the data from its current format (dd/mm/yyyy) to format (mm/dd/yyyy). The data from sheet1 is provided to me by a client and is produced daily through their system which they have told me they cannot change the formatting on the reports. When i try to move data from sheet1 to sheet2 and reformat it only half the data formats into the correct date format. does anyone know how to get the data from sheet1 to format and move to sheet2 properly?
I have attached a sample of the raw output of some call records from our telephone switch. My problem with the date data is twofold; first of all it outputs it in mm/dd/yyyy format, or so it appears. But when I try to change the format to mmm/ddd/yy Excel always interprets the 2nd pair of digits as the month instead of the first two. For example,the following 2 calls are from Jan 1st and Jan 2nd respectively;
01/01/2010 01/02/2010
but excel always treats the 2nd record as Feb 1st. I have read numerous posts from people with similar problems but I can't get any of their solutions to work with my data.
The second problem is that on the 13 of each month the phone switch suddenly stops putting a leading zero in front of the date making the data totally unreadable to Excel apparently. Again, I read a post of a similar problem but can't get their solution to work. My ultimate goal is to reformat the date data so that it is displayed as; ddd mm/dd and leave out the year entirely. Ex. Fri 02/12 as in today...Friday, Feb 12th. I really don't know if the problem is with the data or me at this point.
I am also new to vba (am teaching myself with the help of the guru's on this site!). My problem is to do with dates. I have created a variable in VBA that reads a cell with the value of a date in text format, for example SEP2006. I want to use this value to paste into another excel cell. When I do it actually creates it as a numeric date when I really want to just use it as text, "SEP2006". I have been looking at this for a while and I'm not sure if it's a case of "can't see the wood for the trees". A small example of my code is:
Sub DateValue() Dim dateValue As String Range("D2").Select datevalue=activecell.text Range("a3", [b65536].End(xlUp).offset(0,1))= datevalue End Sub
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'm in the process of setting up an Excel document and I need to be able to have it display dates in a specific format. I need it to express just a month and year such that the month is represented by a letter (A thru L) and the year is expressed as its last two digits such as in the these examples:
May 2012 = E12 Mar 2009 = C09 Nov 2011 = K11 etc.
I want the date to come out in this format regardless of how the user enters it.
I have dates (column E) and times (column F) currently in the format mm/dd/yyyy and hh:mm .
However, my formula in excel deals with dates and times in the format mmddyyyy and hhmm. How do I change the values of the dates to exclude the "/" and the times to exclude the ":" operators?
I want to change the actual value of the cell too. Basically, using custom format mmddyyyy in the date column will not work (because the true value of the date will still yield mm/dd/yyyy.