TRUNC Dates For Vlookup Compatibility
Jul 7, 2008
I have a two sets of data, from two different programs (Excel and Access), were the dates are not compatible with each other in vlookop in excel. The problem seems to be in the decimals of the datevalues, which I have been trying to solve the problem in various ways. The only thing I can get to work is to trunc the dates to get rid of flawed decimals somewhere in the end of the datevalue.
My question to you is how many decimals I should use when I trunc in order to keep the right date in the format of (yy-mm-dd hh:mm). So far, 10 have proven unsuccessful (=trunc(A2,10)), but 8 seems to work. I wonder if I can decrease that number even more and still keep the format above?
View 9 Replies
ADVERTISEMENT
Feb 12, 2009
I have a very serious problem with my excel and TRUNC function. I have created a spreadsheet to show whether a number is divisible by other number or not. If it is, excel would display 0. when it is not I'd expect something like 0,12123. (the number should never be bigger than 1). The formula I use is:
View 3 Replies
View Related
Jun 29, 2007
I have downloaded Excel 2007 as a trial on to my computer (with Excel 2003 in another directory), when I load an excel file developed in Excel 2002 (containing macros) and try an open it with Excel 2007 the message "This workbook has lost its VBA, ActiveX controls and any other programmability-related features" - no problems in Excel 2003
My operating system is XP SP2
View 9 Replies
View Related
Dec 29, 2009
I would like to send a .xls file created in Excel 2007 to a user who will work with this file on a PC that runs Excel 2003. Will they be able to open and use a file created in a newer version of Excel?
View 5 Replies
View Related
Aug 3, 2006
I have a spreadsheet created in Excel 2003. All the non-entry cells are locked. When I protected the sheet I ticked the option for the auto filter to work. The problem is when I send the spreadsheet to my user - she uses Excel 2000. The auto-filter buttons do not work for her but they do work for me. Is there anyway round this? I need to have the sheet locked (apart from the entry cells) but I also need the auto filter function. I've attached the spreadsheet I'm talking about. The protection password is set to "password"
View 2 Replies
View Related
Aug 17, 2006
I have an activity recording tool created in Excel 2003 which works perfectly (ish)
However when some of my colleagues use it on machines running Excel 2000 it falls over at the following definition
Dim dlgFile As FileDialog
this is part of the following sub routine
Sub doCopy()
Application.ScreenUpdating = False
Call Archive
Dim dlgFile As FileDialog
Dim rv As Long
Dim sCreated As String
Dim sClosed As String
Dim wsThis As Worksheet
Dim wbkAOM As Workbook
Dim sThisCol As String
Dim aAOMCol As String
Dim strDate As String
Dim wsCore As Worksheet
Dim wsDiverted As Worksheet
Dim iRow As Integer
Set wsThis = Application. ActiveWorkbook.ActiveSheet
Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
dlgFile.Title = "Select AOM Sheet"
dlgFile.InitialFileName = "*.xls"
rv = dlgFile.Show.......................
View 5 Replies
View Related
Nov 30, 2006
I want to install the new Office 2007 onto my computer.
Will my existing spreadhseets be work with the new version?
But most importantly....If I make a spreadhseet with VBA code and send to to a user with the Office 2003 version - will it still run OK?
View 5 Replies
View Related
Feb 27, 2014
Currently I am working on a workbook where I am using a Save As VBA to create a folder and rename the file. The following code seems to be working fine so far with Excel 2007. Is there a way to confirm that it will work with all other versions of Excel and that my DesktopFolderPath String will work on all users machines?
In addition to these concerns, I am trying to avoid any Runtime Errors and currently, the Compatability Alert is the only thing I can see that is creating one. When it displays, in the event the user is unsure what to do, as most of our field staff is, and they select "Cancel" it displays the error Run Time Error.jpg. Can the Compatibility Alert be disabled? If so where do i add it in my code.
View 3 Replies
View Related
Jan 3, 2013
I have written a macro to loop through files and subfolders to open a specific file for a specific month. I extract the datasets for every excel file into a master template. The code works flawlessly when all of the files have the .xlsm extension. However, I need to open .xls file formats also. When my code opens the first .xls file, named "Staffing Plan.xls" it opens it and automatically makes it "Staffing Plan1 [Compatibility Mode]. I can't open the file in the original "Staffing Plan.xls" file format to read data from. How can I fix this?
View 3 Replies
View Related
Apr 24, 2013
We are using Excel 2007.
I have a macro enabled spreadsheet and I need to save one sheet into a new file that is in xls format. I can do that --- is there anyway to suppress the Compatibility Checker box to make it that much easier?
The new file will not have macros as will just be a data in rows and columns so compatibility. Interesting is the compatibility checker box says there are links to the original spreadsheet, but I can't find them in the connections menu.
View 2 Replies
View Related
Apr 24, 2007
When I open an excel 2000 spreadsheet in 2003, the formulas fields do not populate data just #N/A. I can see data just fine in Excel 2003 viewer.
View 5 Replies
View Related
Jul 17, 2009
I need to populate Sheet2 with the figures from Sheet1, where the data matches.
I think I need a VLookup of some sort to populate the blue box on Sheet2….
Firstly is this possible? If so, how?
View 14 Replies
View Related
Aug 19, 2014
I have attached a sample work sheet that has the directions and a sample.
I want to use vlookup (or any relevant formula) to find a value in a table if it falls between two dates. The sample worksheet does a lot better job explaining this.
For instance, if I want to find 3/1/15, it is between 2/1/15 and 4/1/15, so I want the dollar amount to be $8.75
Sample.xlsx‎
View 2 Replies
View Related
Jul 21, 2009
I have 2 Tabs.
1) Furnaces --> INPUT
2) Report --> OUTPUT
1) I have this speadsheet and every day i entry new information:
Date Furnace# Burner
6/1/2009F-11
6/1/2009F-513
6/1/2009F-512
6/2/2009F-16
6/3/2009F-15
6/4/2009F-14
6/4/2009F-514
6/5/2009F-511
6/6/2009F-12
6/7/2009F-11
2) I want in this tab to get information from the other tab (Furnaces) in order to have an every day control.
Furnace #Burner 6/1/2009 6/2/2009 6/3/2009 6/4/2009.. etc
F-1 1
2
3
4.............................
View 2 Replies
View Related
Feb 2, 2010
I am making a spreadsheet using excel 2003 to calculate sales and tax. Column B is formated for date (1/1/2010). Column D,E, and F are formatted for currency with column D=Total Price, column E=sale price, and column F=tax. In column B I type the date the transaction took place and in column D I type total price. Columns E and F are automaticly filled in with the formulas for same. In column H,I J row 9 I would like to calculate 1st Quarter Total Price, Sale Price, and Tax. Row 10 would hold 2nd Quarter info and so on. I searched for info but can't come up with any thing that works.
View 3 Replies
View Related
Oct 2, 2009
I am using excel 2007, and trying to incorporate a VLookup into some code. The VLookup is comparing dates, which I think may be part of the problem.
View 3 Replies
View Related
Nov 28, 2012
I'm having trouble comparing two lists of clients with vlookup. The problem seems to be that the dates are in two separate formats. I'm combining the information to create unique identifiers (=A1&B1&C1).
example:
A1 B1 C1
Eric Reilly 01/04/85
comes out as: EricReilly01/04/85
A1 B1 C1
Eric Reilly 1/4/1985
comes out as: EricReilly31051
View 3 Replies
View Related
Sep 5, 2013
My purpose is to compare date fields in two worksheets and have the formula tell me if the dates match. I took care to make sure the dates were true date using =datevalue()
=IF(B2=VLOOKUP(B2,'Raw Data'!$J$2:$K$9748,1,FALSE),"Match","NoMatch")
First, it returns #N/A
Second, I used the formula one column over to evaluate invoice numbers and it worked though in the few that did not match it returned #N/A and not NoMatch.
=IF(C2=VLOOKUP(C2,'Raw Data'!$J$2:$K$9787,1,FALSE),"Match","NoMatch")
View 3 Replies
View Related
Sep 3, 2008
Here is an example of what I am trying to do. I have a date in column A on Sheet 1. In Column B I want to return the period for which that date falls.
I have created a list of Start and End dates for each period in Sheet2. The start date is in Column A of Sheet2. The End Date is in Column B. And the resulting Period is in Column C.
Basically if the Date in Sheet1 Column A falls in between the start and end date for a specific period. I want that period returned in Column B of Sheet2.
View 9 Replies
View Related
Jan 4, 2005
I have two tables which both have two columns. One column has dates in it e.g. 01/09/04 and the second column has a number in it. What i am wanting to do is create a combo box that, when i select the date from the list, it brings up the number (that is in the cell next to it) and puts this number into the cell next to the combo box.
I know how to get the combo box and add the dates to the list, but how to bring the dates up.
I have tried creating a combo box with the dates and then formatting it to create a cell link. This link then brings up the first number of the date in the cell i have chosen. I have then selected a blank cell and entered the formula :
VLOOKUP(lookup_value,table_array,col_index_num)
However, when i have tried this, it hasn't worked.
View 2 Replies
View Related
Jan 22, 2009
In column I, there is a list of dates - month and day only
In column J there is a list of dates, month,day, and year.
--I----------------J
1/14----------1/14/2009
1/14----------1/15/2009
1/14----------1/16/2009
1/15----------1/17/2009
in column K, I am trying to setup a Vlookup statement that states if the month and day in column I = the month and day in column J then return the value in column J.
I have the following formula, but it just returns N/A.
View 6 Replies
View Related
Jun 18, 2008
I'm trying to look up a value in Analytics Data searchin for the date in B9 to B & rader.
I get error messages, because i can't find the dates. But I'm sure I have defined the ranges correct. The code works in another workbook with the difference that the table with the variables I'm trying to lookup are in the same sheet as the where I want to place them.
Private Sub Upd1_Click()
Dim rader As Double
Dim lookrng As range
Dim updvalue0 As Double
Dim updvalue1 As Double
Dim updvalue2 As Double
Dim updvalue3 As Double
Dim updvalue4 As Double
Dim updvalue5 As Double .........
View 9 Replies
View Related
Jun 17, 2009
I have a table that shows a row of dates, a row of campaigns, and then a row of values I need to reference. I need to pull the values for a specific date and corresponding campaign.
For example, I need to pull the value for campaign 'notset' on 6/16/09.
I tried the following, but it would only work on the first and last campaign: ...
View 9 Replies
View Related
Apr 5, 2014
does it not like dates? i have columns of data and i wish to search it for each month and then print a months worth. here is part of the data. maybe vlookup does not like dates? i tried putting an index column on the left, 1,2,3,4... but it still would not pick up "Jun"
View 8 Replies
View Related
Jul 11, 2014
Basicly i have a list of information and i need to be able to enter a start data in one cell and a end date in a different cell and then return all the dates between them.
View 1 Replies
View Related
Feb 13, 2014
I am attempting to find a way to search through a database I created based upon every day of the year. My goal is to have a user input a beginning date and end date for a trip, then my functions will return the range specified with each day and it's individual temperature average (in my database).
I also need generating the dates between the start and end dates that are entered by the user. I was considering combining Days360 with an Hlookup that searches through a massive sheet with all the dates for the upcoming year and then going down 1 in the column for the difference in the dates in each column.
Vlookup Help.xlsx
View 5 Replies
View Related
Feb 1, 2009
I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.
View 3 Replies
View Related
Jul 27, 2007
I have 2 spreadsheets. One is a promotion calendar that lists the dates that promotions on a certain product runs. The other is a shipment grid of shipments of that product to the customer.
I want VLookup to find the customer and the dates and then bring me back an asterisk in a separate column to show me that that certain week that product was delivered was a promotion week. The problem I have is using Vlookup to lookup 2 things at once (and if they match to the promotion calendar) and return me an asterisk.
Here is my formula now:
=IF(VLOOKUP(J2&" "&L2,'East Data'!M:AU,2,FALSE),"*",0)
J2 is the customer name
L2 is the week
"East Data" is the spreadsheet with all of the promotions and customers.
View 9 Replies
View Related
Apr 30, 2013
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
View 3 Replies
View Related
Jun 17, 2007
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 Related
