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 have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
I am currently trying to work out what the best way would be to search between two sheets and find out where the differences are. IO have sheet 1 and 2 which both have account numbers and details on. I searching off the first column on both sheets and trying to identify which records exist on sheet 1 but not on sheet 2. The code I have so far is
Sub check() data_sheet = "Sheet1" target_sheet = "sheet2" rowcn = 2 Do If Sheets(data_sheet). Cells(rowcn, 1) <> Sheets(target_sheet).Cells(rowcn, 1) Then Rows(rowcn).Select Selection.Font.ColorIndex = 3 End If rowcn = rowcn + 1 Loop While Sheets(data_sheet).Cells(rowcn, 1) > 0 End Sub
This currently works 50%. Only problem is that this code is not independant to each list, it simply looks at the same cell reference on both sheets and check whether the value is there. It doesnt actually go down the whole list and checks whether it is there.
Columns A, B, and C have data (first name, last name, state) - 125k rows. Columns F, G, and H have data (first name, last name, state) - 5k rows. Some of these individuals are bound to appear on both lists, and I need to know which ones are indeed on both lists. So in other words, let's say I've got "John" "Doe" "TX" in cells F1, G1, and H1, respectively. I want to search through columns A, B, and C to find out if there are any instances in those columns of "John" "Doe" "TX" in the same row.
I'm thinking there will be multiple VLOOKUP formulas involved, but I could be way off.
How do I compare 2 columns and then flag where there is duplication? I want to compare Column B against column C and then flag everywhere there is a match. In this case, Column B may have several thousands of entries (inclusive off all entries found in Column C) and I want to have Excel look through the thousands of entries in Column B and then just place an "x" where there was a match to the values found In Column C. For example:
to set a conditional format and include a function code in it as well?
I want a spreadsheet to change the color/font of a cell/column based on a comparison of a "start date" cell and a "due date" cell. This is to automate the process of highlighting items that have gone past the due date in the second cell. I was trying to do this by looking at the "now()" function and comparing it to the due date.
1. In neighborhoods that have zero units in a given price range I have it to display "-" , because this unit is not actually zero, the data is not available. Therefore a #VALUE! is displayed for the percent because it cannot calculate the "-". How do I get excel to glance over "-" and flag it for no calculation?
2. For the percentages I am having to manually do them row by row. I would like to set it up in a manner that allows me to copy the formula down by column and across by row correctly.
For instance in the percent for Mira Lagos I have =B4/N3 where b4 is the units for mira lagos and n3 is the total. I can drag that formula across by rowto get all the correct percentages for mira lagos price ranges only, but I cannot copy this formula down by column to any of the other neighborhoods. In otherwords I have to do a new formula for each subdivision. e.g.
Grand Peninsula=B5/N3 Meadow Glen(Mansfield)=B6/N3 ...etc
Again I would like to make it so I can copy the formula across by row and down by column so excel will automatically compute it.
Ideally I'm looking for a way to do this with formula's but for the life of me I just can't think of one. Attached is a sample that explains what I'm trying to achieve.
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.
I 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:
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.
I 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)
I 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")...........................
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
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 am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
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 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
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..........................
