I have the following date formatted strings in two Excel cells "17/12/2009" & "19/12/2013".
Now I try to run the following code on these cells:
If Range("B" & i).Value < Range("B" & j).Value Then
But it does not seem to work, as the dates are compared incorrectly.
Any clue how to bring these dates in a VBA readable format?
Using Excel 2003 on XP.
In Workbook1 I have a list of users with a leave start date in column N and a leave end date in column O.
In Workbook2 some of these of users appear with a payment start date in column C and a payment end date in column D.
I need to find out if the any or all of the payment period for these users in Workbook2 falls within the date ranges of their leave in Workbook1. If not, I need to ignore (or delete) the payment record in Workbook2 for that user.
Is this possible using VBA? Mind you, I'm stuck either way as I simply have no idea about how to approach this (possibly ... would first need to work out how to identify if the dates cross and then perform a lookup ...?)
I have two columns (G&H), one has expected date, and one has actual delivery date. The line amounts vary from 2500 to 8000, I need to calculate if column H is greater than column G for each line, if it is not then that line can be deleted.
View 4 Replies View RelatedI am trying to compare the difference between to dates (a person dob and the date of an activity they have carried out and then output the age of the person when this activity was done:
for example:
activity date: 08/01/2008
dob: 05/12/2007
I am trying to figure out how to compare two sets of dates against one another in vba. The data is spread over eight columns. I need to be able to find the smallest date in the first four columns and then compare it to the smallest date in the other four columns, then go to the next smallest and so on.
View 4 Replies View RelatedI need to compare dates in excel (VBA programming). How can I do to Compare two dates. If I wanna know the difference between to dates (days, moths and years)
View 2 Replies View RelatedI am getting a type mismatch error ehen comparing 2 dates. I tried entering all date formatting but it still throws the same error... here is my code. i have highlighted in bold where I get the error. the Error it throws is Run Time Error 13. Type Mismatch Error. I am using office excel 2003 SP3
Dim siebApp As SiebelWebApplication
Dim siebBusObj As SiebelBusObject
Dim revBC As SiebelBusComp
Dim isRecord As Boolean
Dim sRep As String
Dim sCompany As String
Dim sLocation As String
Dim sStep As String
Dim sProb As String
Dim sDate As String
Dim RetDate As Date
Dim CurDate As Date
Const DateCol = 9
CurDate = Now()
RetDate = Sheets("Users"). Cells(DateCol)
r = Sheets("Users").Cells(65536, 1).End(xlUp).Row 'Get next blank row
Sheets("Users").Cells(r, DateCol) = CurDate
'Create The Siebel WebApplication Object
Set siebWebApp = CreateObject("TWSiebel.SiebelWebApplication.1")...........................
My Process goes like this: We receive work in the excel sheet and in the column K(Period will be in YYYYMMDD)
First I want macros by which I can change YYYYMMDD TO MMDDYY.
then
It should compare column J and column K.
see the below example.
Period date (column K) Product end date (column L)
Column J column K
20131031 10/31/2013 = If column J & K dates are equal then "No action required"
20131031 7/31/2013 = If column J is higher then column K dates then "Action required"
20130930 11/2/2013 = If column J is lesser then column K dates then "No action required."
I need comments in column Q.
I have two enormous lists of dates. How do I automatically compare them to ensure the date on one comes after the date on the other one?
They're formatted as dates. Christmas Day 2000 (UK) did say 25/12/2000, and then when I reformatted it it automatically changed to 25 Dec 2000, and so on.
I've been trying for a few time to make a sub, on VBA, that writes, in a specific column, " " or 1 depending on the date that is inserted. The thing I can't quite do is:
1 - I have a sheet where the different projects of a company are stored (name, starting date, ending date and person responsible) and where new projects can be added from a UserForm
2 - I also have another sheet were costs from the projects are stored (one line with cost 1 from project 1, another with cost 2 also from project 1 and cost 3 from project 2, etc) and where I can also add a new cost with another UserForm (this cost as also a date that is added, to when it happened)
3 - What I need to do is, on the costs' sheet I need to create a sub that in the last column adds me 1 if the date of the cost added is not a date between the starting and ending date of the project that it corresponds.
Currently I am working on a Userform based Macro that reads date and time from user and compares it with the date and time present in a cell in excel sheet(in mm/dd/yy hh:mm format). If the date and time value entered in both places matches then it will fill green colour in the cell.
I tried to convert the date present in excel sheet into a string using Cstr but it is not working. I have attached the code below for your reference.
Dim lr As Long, i As Long
lr = Sheet5.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lr
If Cells(i, "D") = "FIT" Then
If Left(Sheet5.Cells(i, 14), 1) = "M" Then
[code].....
I am trying to set up a spreadsheet to track certification expiration dates and things of that nature at my volunteer fire department. At my career job, we use specialty software like Firehouse, but that is way too expensive so I must settle for Excel..or maybe Access.
Basically, I need a formula that will look at todays date and compare it to the date in a given cell and then somehow differentiate thise which are expiring at a given intercal.
For example, if someones EMT was expiring in 6 months it would turn yellow, then at 3 months turn blue, then red at 1 month then black if it expires.
It don't necessarily have to be just like that, just some way to differentiate depending on the how close to expiration it is.
I need to compare dates from two workbooks. The dates are not entered in the same format. In order to compare them I store them in Date variables.
However, some dates entered do not have a standard date format (for example, day is missing) and I get an error when I try to store them in a Date variable.
The solution I thought out (most likely a bad one) was to temporarily re-dim the variable from Date to String when an error occured. The macro would then be able to store the incomplete date and do the comparison.
So, basically, the variable would be dim'ed once as a Date, then possibly dim'ed a second time as a String, and if this occurs, then it would be dim'ed back to a Date once the erroneous date would have been treated as a String.
My problem is that it doesn't appear to work if I use the dim statement because of "duplicate declaration in current scope". Is there a way to re-dim a variable as something else within the same macro?
Battling through a problem, but would like to get some feedback on some failing code.
basicly created a bunch of macros to get 2 columns. One has last months dates, and the other has the new ones.
I want it to check if the date in columnH is greater then columnJ then update (i have vlookups to get this info with offsets). You see if there is no result on columnH it means it does not add a date.
heres the code that i have. The error is that i am using an invalid statement in the case ( i suspect the >=)
I have 2 columns directly next to each other each containing dates.
Example 1:
F2 = 5/23/13
G2 = 5/23/14
Example 2:
F3 = 6/6/13
G3 = 4/11/14
I wanted to do a conditional formatting that would highlight the cell in
column G if the Month/Day doesn't match the Month/Day in column F
Is there a way to do this with Excel only looking at the Day/Month and ignoring the year??
I have several old spreadsheets that contain patient information. Except for names, dates of birth, and dates of surgery there is nothing consistent about them. Not only that, the old spreadsheets were in various stages of compiliation and recompilation when they were abandoned. So now there are multiple duplications of old names added to lists of new names spread across multiple spreadsheets. I have to identify names that are not already in my database and add them.
I have a formula that I found on the internet that I can use to check if a patient’s last name in an old spreadsheet is one that already appears in my main database. In my attached example the formula looks like this:
I am fitting 2 sets of data to match eachother. I want to have as much data as possible but only for the same dates of each dataset.
I have 4 coloumns.
Dataset 1: A is date B is data
Dataset 2: C is date D is data
I want to compare A vs B one row at a time. If they are not similar it should delete and shift up the values in A+B or C+D according to the lowest value (date) of A vs B.
Remember that if the macro just deleted and shifted up it should compare the same (undeleted) value against the new value that just shifted up in the other coloumn.
I have a column of dates (B2:B1500) that I want to compare to a single date (A1).
If the dates in Column B are older than the date in A1 I want to delete the entire row.
I'm using Excel 2003.
I have a Userform where you can enter a date and select a comparison operator from a combo box. Any one of these (= < <= > >= <>)
In VBA I then load a range into a dictionary object and then loop thru it to compare the dates in the dictionary to the date selected in the userform but I can't get the dates to compare correctly. I think it has something to do with the Evaluate function comparing the dates as strings but I'm not really sure.
VB:
For Each strKey In dictValid.Keys()
If Not Evaluate(CDate(dictValid(strKey)) & cboDteOperator.Value & CDate(txtDteSel.Value)) Then
I have a spreadsheet that captures task start and end dates similar to project. It currently calculates Networkdays correctly, so if you have:
1. 1/1/06 - 1/31/06 (22 Days)
2. 1/1/06 - 12/1/05 (- 22 Days - showing dates have been entered incorrectly.)
3. 1/1/06 - No End Date (-27655)
=IF(ISERROR(NETWORKDAYS(E23,F23)),"NA",NETWORKDAYS(E23,F23))
My concern is with #3, is there a way to instruct the formula that if columna and columb are not filled in,the result should be blank. Previously I had it where it indicated NA - but the problem with this is - while it appears fine in Excel, when I pull it into Access to report on I get the -27655. This is throwing my reporting off.
I am trying to do a macro that looks to see if the date is different from one cell to another going down in rows. I got it to work until the cell is empty then it says the date dont match but I just want it to stop the loop. so it should go down a list check to see if the date is the same all the way down the list, stopping if cell is blank, if not give a message box, if it is the same stop.
Is As follows
Dim rowNum As Integer, colNum As Integer, currCell As Range
rowNum = ActiveCell.Row
colNum = ActiveCell.Column
rowNum = rowNum + 1
'get first cell
Set currCell = ActiveSheet.Cells(2, 3)
'loop while cell not empty
Do Until currCell.Value = " "
If currCell.Value = ActiveSheet.Cells(2, 3) Then
If currCell.Value = "" Then
End If
I have built a macro that tests to see if a date in a range is before the Period Start Date, and if it is, then the labor rate in that same row (3 Columns Over) should be escalated by the appropriate AWI. My code below works, but I am wondering if there is a better way than using offset(0,3) to prevent a code breakdown if columns are inserted between the Date and Rate columns. The date range is names "DATES" and the rate column in names "RATES"
Sub CountTheCells()
Dim cell As Excel.Range
Dim i As Integer
For Each cell In Sheet2.Range("DATES")
If cell.Value < Sheet2.Range("POPS") Then
i = i + 1
End If
Next
MsgBox ("You have " & i & " Rates that will be escalated b4 the POP Begins")
yesno = MsgBox("Would you like to Escalate the Dates and Rates?", vbYesNo)
If yesno = vbNo Then
MsgBox ("Not Done")
Else..........................
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx‎
I have two ranges of numbers stored as text on two different sheets.
The first one is from product code and the second is from product category.
The problem is both ranges are of different lengths and I have to find out if a product from the right is part of a particular product category. Even if the length is different the first digits are indicative of the belonging of a code. For example 1234 and 12345 are “family”-their first 4 digits match.
Just to give you an example of what is desired:
Category____ Code
2200 ________22002
2323________ 232347
So, the loop should do the following:Compare the first string from the “Category” column to each and every entry on the right, if a match exists (we have no match here for 2200) write “ok” next to it.
Next trim one digit from the right from every string in the “Code” column.
Compare same first string from the “Category” column to each trimmed string from “Code” column (here we should have a match 2200=2200)
Write “ok” next to it
Now the loop goes to the second string from “Category” column and for this one we will have to trim 2 digits from the right of each string in “Code” column to achieve the result (2323=2323) and so on.
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 RelatedI'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011
control ~ (overview)
Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261
2nd view - system dates are getting converted into 1911
Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911
Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).