Date Comparison From Siebel

Feb 14, 2008

I have an issue in comparing 2 dates. It gives a data type mismatch error

Private Sub TextBox12_Click()
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 CurDate As Date
Dim RetDate As Date
Dim datmins As Integer
CurDate = Now()
Const DateCol = 9
RetDate = ThisWorkbook. Sheets("Users"). Cells(DateCol)
r = ThisWorkbook.Sheets("Users").Cells(65536, 1).End(xlUp).Row 'Get next blank row
ThisWorkbook.Sheets("Users").Cells(r, DateCol) = CurDate....................

I have highlighted in bold where i do the date comparison. Here are the date formats [Last Updated] = 2002-11-08 13:19:03.000

[RetDate] = 2/13/2008 15:59:18

View 3 Replies


ADVERTISEMENT

Date Formula With Variable Date & Comparison Operator

May 14, 2008

I have a date in cell A1. I have an operator in cell A2. This info is then merged in to cell A3 with the following formula: =A2&TEXT(A1,"dd/mm/yyyy"). My data range is called "wc_date_of_loss". I want to use this info in a SUMPRODUCT formula (as the dates and operators can be changed by the end user). What's the correct syntax for that?

=SUMPRODUCT(--(wc_date_of_loss&A3))
=SUMPRODUCT(--(wc_date_of_loss=A3))
=SUMPRODUCT(--(wc_date_of_loss=TEXT(A2&A1,"general,dd/mm/yyyy")))

View 6 Replies View Related

Date Comparison

Mar 23, 2009

How in VBA can you evaluate a cell in the "general" format and in the "date" format? I'd perfer do this entirely in VBA without use of a helper column in Excel.

View 9 Replies View Related

Date Comparison/validation

Jun 22, 2006

I am constructing a spreadsheet which is intended to highlight Just in time and late jobs. I have headings job number/Customer/Delivery Date/ Revised Manufacture Date. The first three are already populated by extracting data from our erp system and we enter the revised run date ourselves.

What I am looking for is a formula which shows if the revised run date entry is a day prior to the delivery date then populate the last cell with JIT and colour the cell yellow. Or if the revised run date entry matches or is beyond the delivery date, populate the last cell with LATE and colour the cell red.

View 4 Replies View Related

User Defined Date Comparison VBA

Feb 14, 2008

What I need is a Macro that will allow the user to enter a date (ex: 2/14/2008) and then look in column H for any dates prior to the user entered date and higlight the corresponding cell in Column A.

I created the macro below from one in another workbook of mine and when I run it, I get a Run-Time error '13' Type Mismatch. When I go to Debug this code is highlighted in yellow: lookVal = InputBox("Securitization Date")

How can this be fixed to accept a date entry in the Input Box?

Sub SecuritzationDate()
Application.ScreenUpdating = False
Dim lookVal As Long
lookVal = InputBox("Securitization Date")

For i = 2 To Range("H" & Rows.Count).End(xlUp).Row
If Range("H" & i).Value < lookVal Then
Range("A" & i).Interior.ColorIndex = 40
End If
Next i
Application.ScreenUpdating = True

End Sub

View 9 Replies View Related

Add Siebel Records To Sheet

Feb 13, 2008

I have a question with vba coding in excel. There is a Refresh button in my Excel Sheet. Once I click Refresh button, I fetch data from another application called Siebel. I am able to go this far. I fetch multiple records from Siebel.. I need to add them all in excel sheet. I get five columns for each record I get from Siebel. I need to add them accordingly to excel sheet.

View 6 Replies View Related

Searching For Data In Siebel Database

May 13, 2008

I'm trying to write a macro in Excel that will search certain values a Siebel database. I'm already working with a macro to ADD data to the database, so I have all the connections taken care of. But now I need to pull data from it. In looking for how to do this, I see the setsearchspec method that I need to use to set up the search then the ExecuteQuery to run it to get the pointer. But before that, I have to create the referencing object. I've noticed in several examples here and on a couple other sites, these two lines:

Set busObject = SiebelApplication.GetBusObject("Revenue")
Set busComp = busObject.GetBusComp("Revenue")

So here's my question: in all of the examples I've seen, they used "Revenue" as the argument for these methods. Does it really matter what we use there? Or this just essentially a place holder? I've looked at some of the online documentation and was unable to glean an answer from that.

View 2 Replies View Related

Simple Date Comparison Formula - When Two Dates Are Equal To Each Other

Feb 10, 2014

Report I generate once a month that checks to see if tasks are done on or before their due date. Sometimes it works, and others it doesn't...

The basic formula is: [Code] .....

If they closed their task by or before the due date, the condition should be true and it should output "Compliant" beside their task. Otherwise their "Over Due".

The forumla works except when they close their task ON the due date. No matter how I tweak the forumla, it thinks the condition is false as if it cannot tell when the two dates match.

View 1 Replies View Related

Loop A Comparison And On Match Return Earliest Date?

May 25, 2012

Using online examples I am usually capable to reconstruct whatever I need. However, it's the combination of things I need to do now, which has left me banging my head on the keyboard for days now

Here's the data I am working with:

Sheet 1:

Sheet 2:

And here's what I need to get done:

From sheet 1, select first ID entry from Column A.Find matching ID in Column A of sheet 2On match, find in Column B the earliest date belonging to the concerned IDCopy that earliest date next to the corressponding ID in Column B back on sheet 1Return to step 1 and repeat for next ID entry. Do until end of list (sheet1)

So the result should look as following on sheet 1:

The major issue I am having is the combination of step 3 and 5. Because it probably means something like moving through an array that's within an array through which one is moving. And I am just missing that bit of experience that allows me to make that thinking step. I just keep falling off if you know what I mean...

View 3 Replies View Related

Excel 2003 :: SUMPRODUCT Date Comparison Formula

May 7, 2014

The following formula works perfectly in Excel 2003:

=SUMPRODUCT((A2:A10="A")*(b2:b10="B")*(c2:c10="C"))

However, I've got a fourth column that contains either a date or a blank cells; I want to limit the formula to only include certain dates in the sum. i.e:

=SUMPRODUCT((A2:A1000="A")*(b2:b10="B")*(c2:c10="C")*(d2:d10>="01/04/2014"))

Rather than an error it's just retuning an incorrect number.

View 6 Replies View Related

Excel 2010 :: Date Comparison In Pivot Table Error

Sep 28, 2011

I have to generate a report using a pivot table, that tells me what work was done on time and what was late. Entering a formula outside the pivot table sees the scheduled start date drop downs as "blank" and says they are late when they are not. I cannot seem to use the value fields when I try to create a calculation field within the table. I use 2010 at work, 2007 at home.

sep 11-17
FGHIJK10Count of WO No.

=IF(G12>=H12, I12," ")=IF(G12

View 3 Replies View Related

Comparison Of The Two Lines?

Feb 19, 2014

I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :

7,7,K,A,8,7
K,6,7,7,A,9

To reach the conclusion I need to build six such functions:
=1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66))
=1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66))
=1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66))
=1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66))
=1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66))
=1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))

The result is 4

Is there a single formula that will give the result ?

Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?

View 8 Replies View Related

If, Then Comparison With Result

Jun 12, 2009

Compare 2 columns of data and have a new 3th column tell me the result of the query based on some conditional information. So, here's an example.

Column A = Programmer Assigned (Yes/No)
Column B = Project State (concept, plan, develop, qualify, rollout, etc)

I'd like to compare the values in A to B and have the formula tell me if the programmer should be assigned in that phase or not. So, in english.

If A = No, then look to see if B = "develop" or "qualify", if it does, populate C with "Update required". If it doesn't, populate with "Correct".

I hope I explained it well enough for you all to help. I originally looked into using conditional formatting to just format the cell differently if I needed to perform an action.

View 3 Replies View Related

Comparison Macro

Oct 24, 2008

to compare the values of data on one sheet (sheet1) with that of another (sheet2) to see if there has been any changes between the sheets over the previous week. If a macro could go through my data on sheet1, compare cell-by-cell the data that's in sheet 2 and highlight in red font any cells that have different values that would be awesome.

The macro would need to leave the "fill color" of the cell as it was.

View 14 Replies View Related

Comparison Of Two Spreadsheets

Mar 6, 2009

I have two spreadsheets, The spreadsheet #1 has the information of two years and the Spreadsheet #2 is a montly report. The Spreadsheet # 1 hasta on the column A a number of transaction, and at the column Q the invoice number. The Spreadsheet # 2, only has in the column A the Invoice number. I need to find in the Spreadsheet # 1 the Invoice numbers that match the Invoices that I have in the Spreadsheet # 2 by bringin the "Number of Transaction".

For example:
Spreadsheet 1:
Has on column A3 the Number of Transaction # 0123, and at the column Q3 the Invoice number 555. At the Spreadsheet number # 2 I have the INvoice # 555 located on A10. I need to know what is the transaction # by adding a formula in a new column (G) so I want to have the Transaction # 0123 in the new column added G.

If I do this manually it will take me hours since these report and the Master is so big, and right now I am doin it using Ctrl-F

View 15 Replies View Related

Comparison Of Two Columns?

Oct 11, 2011

I have two columns of dates. I want to see if there are duplicate dates and highlight them in the first column. What is the easiest way to do this?

View 9 Replies View Related

Comparison Of Lines

Jan 22, 2007

There are four lines of numbers, in all line 8 numbers.

1 1 2 2 3 1 1 2
1 2 3 1 2 2 2 1
2 1 1 3 1 3 1 2
1 2 2 2 3 1 1 2

I would like to find a function that will examine, if there are similar lines according to my definition, and several.

my definition is 7

For example that I gave, the result in the wanted function needs to be, yes (Line 1 and line 4) and several-One time.

View 9 Replies View Related

Data Comparison

Mar 27, 2009

I have two columns of items that need to be compaired. What I need is the items in column B that do not match any of the items in column A need to be put in Column C. How can I do this? The Data in Column A can not be re arranged. Column B can be sorted.

View 9 Replies View Related

Comparison (Strings)

Jul 28, 2009

I am trying to compare two cells to see if its two values are the same. Some of the cells have a space in between numbers or a symbol like "-"

Ex.

one cell's value is
1213180IN

and the other cell's value is
1213180-IN

the program I have written marks these as different because of the "-" in the second cell....is there a way, maybe with a string function to overlook that "-"?

View 9 Replies View Related

Formula For Comparison Of Data

Jul 28, 2009

I have a spreadsheet and I'm comparing data from a room booking system and a spot check on the room to see whether it's actually used. I have a column which I want to show whether the information agrees.

column G - I have a group size recorded that should be in a room.
column H - I have "yes" or "no" as to whether there was anyone in the room when it was checked
column I - I have the size of the actual group in the room.

Is it possible to create a formula that will fill in column J with the answer yes or no depending on whether it agrees.

It doesn't matter whether the groups sizes match, it's just a case of yes, someone was booked into the room and there was someone in when it was checked

OR

No, there was a booking supposed to be in the room but there was no one in it when checked

OR

There was no booking but there was someone in the room when checked.

View 4 Replies View Related

MMDD Comparison In Two Cells

Jan 18, 2012

I need to compare the MMDD in two cells. Example: If the Accrual date is 09/07/10, then I need to know if it falls in the payroll period date of 01/12/12. I just need the MMDD compare to put in my IF statement.

View 1 Replies View Related

Delete Rows Via Comparison Using VBA

Aug 21, 2007

I have two tabs in a workbook. Each has a column containing a unique numerical identifier. What I want to do is create a macro that will loop through the column on tabA, verify it against the column on tabB and when a match occurs delete the row on tabA. The end result should be that only the new items on tabA will remain. Let me know if this is sufficiently clear or still a bit vague.

View 9 Replies View Related

Box Comparison With Error Message

Aug 24, 2009

I am writing some code to do a comparison between two cells X2 or AB2 with an X which checks to:
1. make sure one of the two are filled, or
2. both are not filled.
Below is an extract of the spreadsheet I am working on. I need to check to make sure one of the boxes is checked. If both are empty or both filled with an ("X"), I want a message box to tell me to correct the error and stop the macro from continuing. If only one is marked with an ("X"), I want the macro to continue but don't know how.
 
RSTUVWXYZAAAB
2IN PROGRESS:  COMPLETED:  

View 9 Replies View Related

Create Comparison Code In VBA

Jan 5, 2010

I have to do monthly comparisons of two excel worksheets, one being a vessel timesheet and the other being an excel download from manpower software that we use. The comparison that is being made are days of the month, whilst the unique identifier between the two sheets (To make it easier to understand I will call them sheet1 and sheet2) is an employee ID. Both sheets, alongside the name and employee ID, list all the days of the month that employees have worked. If they have worked, there is a "1" under that day, if they have not the cell is blank.

What I currently do manually is:

1: Search for the employee ID (and/or name) in sheet1 and compare the days with the employee ID in sheet2.
2: Highlight the differences
3: Move onto the next employee ID

All differences are highlighted in sheet2. Differences highlighted are in the days only, where if any days are missing/extra in either sheet, then the particular cell(s) are highlighted in yellow. If an employee is missing from sheet1 but is in sheet2, then all the days are highlighted (in sheet2). If an employee is missing from sheet2 but is in sheet1, then that particular employee is copied to the bottom of sheet2 (after last row).

As this is horribly complicated to explain, I can attach an example if this is sounding rather inexplicable? Is it possible to attach a xls to a thread?

View 9 Replies View Related

Data Comparison Formulas

Mar 22, 2007

With a set of data in colums by quarter and department names in by row I need to answer build a formula that answers simple questions like "what department had the fewest sick days per quarter?" and which department had the most sick days per quarter?"

View 4 Replies View Related

Variable As Comparison Operator

Aug 16, 2007

Can you use a variable as an operator? For example:

Dim op
op = ">"
If Var1 op Var2 Then MsgBox "It Works!"

I have parameters stored in a database and each parameter includes an operator. When I compare a given value to the parameter, I was hoping to do that dynamically by extracting the operator from the parameter and using that in the comparison.
Otherwise, the only way I can see to do it is create a Function with a Select Case list for all the operators to check the parameter, do the comparison, then return the value to the Function.

View 2 Replies View Related

Exact Comparison In AdvancedFilter

Mar 3, 2008

I am trying to use the advanced filter with a not equal to operator, where the requirement is that the output not match an exact text string. I have tried <> and <>"???" (where ??? is the text string) and neither filter is working.

View 2 Replies View Related

String Comparison With Conditional Formatting?

Jan 16, 2013

I have a column of cells with compound if statements like this:

VB:
=If(And(N10,K10<>""),"X","")

I'm trying to conditionally format this column of cells so that if the cell changes from this formula it'll turn orange. I've tried different variations of the following:

VB:
=N10<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
=mid(N10,1,len(N10))<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
=left(n10,len(n10))<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"

Basically, I'm trying to do a string comparison of the formula contained within a cell (N10) to a string that is assembled on-the-fly. If they don't match, then the cell should fill orange. I think I'm most of the way there, but can't quite get it to work. While this is simple to do with VBA, I'm rebuilding a lot of the functionality of this sheet using the functions available through the Excel UI -- users always forget to enable macros and always save copies as xlsx.

View 2 Replies View Related

How To Plot Comparison Of Only 3 Points Against 9 In Graph

Mar 27, 2014

I'm stuck at a point in a graph that I am creating. Attached is as far as I have gone and now i'm having a gap on how to take the 2006, 2007 which is already in graph and add in how these years compare to guidelines visually in the same graph?

for instance ratings 1-9 are grouped in guidelines 1-4, 5-7 etc. How do i add in the 3 guides against each point? I have thought about adding a secondary axis but not how best to demonstrate the range of 1-4 to appear behind or above those points?

View 1 Replies View Related

Comparison Of Cells With Text Input

May 15, 2014

I am working on Excel file with names and I would like to have a possibility to check if all characters from one cell are mentioned in another one.

The background of the problem is that the names are provided in different types (e.g. O.M.Izgala, M.O.Izgala, or only Izgala) so I believe I should first remove any spaces, dots etc. to have clear continuous text.

Finding a formula (if any exist), which can check if all digits from one cell are in the second relevant cell regardless the order of the digits. I would like to show you short example:

name needs to be checked cell to compare expected results
OMIZGALA MIZGALA no (as O is not in the relevant cell)
OMIZGALA MIZGALAO yes (as all digits are mentioned in the relevant cell but in different order)
OMIZGALA MOIZGALA yes (as all digits are mentioned in the relevant cell but in different order)

if it can work in Excel.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved