I have crated a large excel spreadsheet where dates are in 3 columns ie day/month/yearIs there a way to get the dates in a single columnthanks in advancebbmf

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link __ http://downloadreimage.com/download.php__. (This link will automatically start a download of Reimage that you can save to your computer.)

Assuming that day, month and year are numbers in columns A, B and C=DATE(C1,B1,A1)

2 more repliesHi,

I am a beginner in excel. I have tried to look this up but could not find a solution.

I am trying to sort two columns of dates.

I have attached a sample with dates in column C and D.

Each row is a different event. Each event has one or two dates. Column C is always earlier than Column D.

I would like to sort it from the oldest to newest date in column C, but sometimes column C may be empty and I would then like it to take the date in Column D into account and sort it in as well.

Please let me know if I need to be more clear on anything.

Thanks!

you could setup a helper column and use

=if(C2="", D2, C2)

and then you can sort based on that new column

I have no idea if this is even possible, but I would like to create a spreadsheet that auto-populates a series of dates based on one date that the user enters. Let's say I have a goal date to complete a project, and for every project I work on, something needs to happen 6 weeks prior to the goal date, another thing needs to happen 4 weeks prior to the goal date, and something else needs to happen 1 week and 5 days before the goal date. I would like to set up 4 cells in Excel: one for the goal date, and three for the various things that need to happen leading up to that goal date. I would like to be able to enter the goal date into the main cell and have the other three cells auto-populate with the correct date based on what I enter in the goal date. For example, if I enter a goal date of 11/01/2016, I would like the other three cells to automatically populate themselves with: 09/20/2016 (6 weeks prior), 10/04/2016 (4 weeks prior), and 10/20/2016 (1 week and 5 days prior). Is this possible? Can Excel return dates in that way?

Sure, it's easy. Excel internally stores Dates and Times as numbers. Each day is a whole number and time is the decimal portion of that day. See here for a pretty good explanation of how Excel deals with Dates and Times as well as links to all the various Date and Time functions that Excel offers:http://www.cpearson.com/excel/datet...As for your question, simply subtract the number of Days required from the cell with the Goal Date.With 11/01/2016 in A1, use this to return 9/20/2016:=A1-42(42 is 6*7, or 6 weeks.)I'll leave the other two formulas to you. ;-)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesI am not sure if what I want to do is possable or not. I have a spread sheet with three colums, date, total, daily total. First of all the dates in will very and have multiple entries for the same day or just one depending on service. I want to make one entry in the daily total column on the last entry of the day with the total for the day. So i need to check dates in column date and if the same add the corrosponding row in time for one entry. And I am working with Excel 2003. Is this possable? If so can someone please help me!!

Try =SUMIF()If your data looks like this:

A B

1) 9/30/2010 1.00

2) 10/1/2010 2.00

3) 10/1/2010 2.00

4) 10/3/2010 2.00

5) 10/4/2010 1.00

6) 10/1/2010 1.00

7) 10/6/2010 1.00

8) 10/7/2010 1.00

9) 10/8/2010 1.00

Then to find the total for the date 10/1/2010 use this formula:=SUMIF(A1:A9,"10/1/2010",B1:B9)You could also do it by selecting a date from your date column like this:=SUMIF(A1:A9,A6,B1:B9)MIKEhttp://www.skeptic.com/

Ok I am trying to print a spreadsheet format list. I have about 30000

title-artist entries in 2 columns. I am basically trying to print pages 1 & 2, 3-4, 5-6 and so on the same page without shrinking them way down. I want it to be just like I printed 2 columns but with 4 instead. I want to use the left side of the page for page 1 and the left side for page 2. Any help would be greatly appreciated.

Michael

Welcome to the board.

http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=124&utilities=Format ?

For some reason as I have added dates to cells, they are not getting converted to the Date type I have selected: 15/03/2009I type in my entries in this format:March 15, 2009 (usually it converts immediately but not this time)I've tried selecting the columns of cells with dates, Select>Format>Cells>Date and Type but nothing changed (although one date was converted to 03-15-2104Any suggestions?

Dat's cuz they're probably not being recognized as dates by Excel. My guess from afar is that the cells are formatted as Text and aren't accepting any other formats. Excel is finicky like that sometimes.Try this with one of your stubborn "dates"...let's assume it's in A1=DATEVALUE(A1)Now format that cell anyway you want.Then do a Copy...PasteSpecial...Values to eliminate the formula.

7 more repliesHi Guys,

Ive opened my excel this morning and instead of there being numbers down the side and letters across the top ive got numbers down the side and across the top so i cant do any statements? Does anybody know what this could be, and why its happened?

Thanks

Hi gazmysta,

Tools/Options/General untick the R1C1 reference style box.

BR

Hew

Hi guys,

Is it possible to hide certain columns in a worksheet? (Excel 97 on Win98se). We have a spreadsheet that has columns A-G then BF-BJ, then BM-BR. The person that created the spreadsheet is on vacation this week, so we can't talk to them. The person using the spreadsheet now, says there is more information that is needed to get this information currently on the spreadsheet. Is it a reach to think that there may be more info in possibly hidden columns? Aside from renaming columns, is there any other way that someone would go straight from G to BF?

Thanks a lot for the help!

J

I have some data on Excel. Address numbers are in one column and the street names are in another column. Is there a way I can copy all the information in one column (address numbers) and merge it into another column (street names)?

Rob

if the address numbers are in a1 and the street name is in b1, paste =A1& " " &B1 in c1. This will place the number and address in c1.

Tom

When I started using Excel 2007 this morning, I noticed that column headers were showing as 1,2,3 rather than the usual A,B,C.I successfully googled for the solution and all is now back to normal (by removing the tick from "R1C1 reference style" in Excel Options > Formulas).Purely out of curiosity, I wonder how this happened because it was something I was unaware of until this morning and I am as confident as I can be that I did not put that tick in there in the first place. Someone (reliable source) sent me a spreadsheet to review yesterday. Could that have been the source?

Possibly or you could have inadvertently held down ALT and typed TOC then released ALT and pressed Enter.

2 more repliesEldest daughter driving me mad.At business she is working on a database in Excel.She tries to shorten the DOB entry as for example23/11/29 for someone born in 1929 but it comes out as 23/11/2029 yet when she enters 23/11/31 it comes out as she wants it as 23/11/1931. Is there something we need to format in Cells/Date?Brambles

and have the same problem! Using excell 2000 and my cells are formatted correctly. Very strange, however there are lots of very helpful people on this site and here's hoping.Taffy

3 more repliesFilename: SysInfo.exe

Full Path: c:\Users\JIMBO\AppData\Local\Microsoft\Windows\INetCache\IE\RBAGCWGH\SysInfo.exe

____________________________

____________________________

Developers

Not Available

Version

1.0.0.2

Identified

6/27/2016 at 5:17:31 PM

Last Used

Not Available

Startup Item

No

____________________________

Many Users

Thousands of users in the Norton Community have used this file.

Mature

This file was released 4 years 8 months ago.

Good

Norton has given this file a good rating.

____________________________

Source File:

sysinfo.exe

____________________________

File Thumbprint - SHA:

8243b4ea661b060fe8cf4babc11ab5f51eadd28a0c9d66303183e8eceace8234

File Thumbprint - MD5:

Not available

I have been using this formula to let clients know when their bills are due:

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+90)

this ends up looking something like this: 1/1/04

however, I have had a request from them to have it look more like this: 1-Jan-2005.

Any help would be greatly appreciated. Thank you.

I have a friend from california he has got office over here in europ but because of work he needs too change his dates in excel too american format how can i do this?

I have a sheet in excel which I input my daily results. Some dates or blocks of dates are missing as I am unable to gather the data. (vacation, travel, etc)Column A is the date (mm/dd/yyyy)Column B is the result (0.00)I have another sheet with the following:Column A has a list of all dates (month and day only) (366 rows) Columns B through M have the year in row 1I need to lookup the month and the day and attach the year based on the column year (this is easy)=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2)BUT I need the exact date data returned. VLOOKUP returns the closest lower value.If no exact date then return 0 or "".Is there an easy way to do this in Excel without writing code?Thanks for helping in advance!

If you want an exact match, you need to use FALSE or 0 in your VLOOKUP as the optional range_lookup argument:=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)Review the VLOOKUP Help file to see the options for this argument.If an exact match isn't found, this will return #N/A, so wrap the formula in an IF(ISNA()) function to get a "" of 0 if the VLOOKUP evaluates to #N/A. This will return "":=IF(ISNA(VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)),"",VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0))

4 more repliesI've got a strange one, I think !!!Current date is 16 Jan 2005, and in my module I say Dim CurrDate As LongCurrDate = Format(Now(), "00000")This sets CurrDate as 38369Later in the module I want to compare this date with one from a cell ...Dim MyNewDate As LongMyNewDate = Range("N" & MyStoreRow) ... value is 17 Jan 2005The value being returned in MyNewDate is 38369, not, as I would expect, 38370 ...In another worksheet, the new date in Nxx is 18 Jan 2005, and this returns a value of 38370, not 38371 ... Any ideas as to what's happening here ?!?! Is my Current Date wrong ? Is this a known 'problem' ?!?!?

I'm not sure what is goung on but you would be better off working with Dates rather than LongsSub test()Dim mydate As Datemydate = Now()MsgBox mydateEnd Sub

6 more repliesI have two columns of dates, A and B. Column B is just As date plus 30 day. What i want it to do is, whenever i update column A's date, i dont want column B's date to change. how do i do that??

i dont want column B's date to change.Remove the formula that updates B?MIKEhttp://www.skeptic.com/

24 more repliesCan anyone let me know the formula for coverting a date held in excel as yyyymmdd into dd/mm/yyyy.Many thanks

Assuming that is a text value try=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))where A1 holds the text date.

2 more repliesHi All,I am updating a old spreadsheet and wanted to add some work days down the side of it. The problem I have is that I only want Mon-Friday dates to be listed. For example....12/3/201212/4/201212/5/201212/6/201212/7/201212/10/2012etc.....For some reason I can't get it to work right. In this example you can see that the first work day for Dec is the 3rd and that the 8th and 9th are passed over because they are a weekend. I know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it. Anyone have any ideas?ThanksSpoiler

Quote from: Spoiler on December 14, 2012, 10:10:41 AMI know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it. Are dates for all rows part of the existing data? If so, then one approach might be to add a column to show the day of the week and then use a Filter to show all days except Saturday and Sunday.

6 more repliesI'm trying to set up a spreadsheet so that each time I open it, the time between two dates is calculated. The two dates would be today (the day I open the spreadsheet) and a date about 10 years away. Any help would be appreciated.ThanxDD

For examplein A1 30/03/2017in B1 =TODAY()in C1 =DATEDIF(B1,A1,"d")

10 more repliesI have a column of calendar dates (A1:A100) that do not include Saturdays or Sundays. I have certain production times for various products that I must base off of these dates and am having trouble counting an amount of days that doesn't include any Saturdays or Sundays in my count. Example below.

10-02-2006 I need to add 18 production days to this date. Workdays only being Mondays-Fridays, I need the answer of the formula to equal 10-25-2006.

Can anyone assist?

Ron

My english is bad. But try to understand and help me!!!

I need more columns to Excel 2000 or XP. The max account is 256 columns and I need 500 columns.

Windows 98 or 2000.

Excel 2000 or XP.

Hi

I'm afraid you're out of luck. 256 is all you've got. It's debatable whether MS will increase this in the future, but don't hold your breath.

Just curious, why do you need 500 columns? Perhaps there is another way round your problem?

Regards

Please help. I need to find a formula that compares any value in one column to any value in another. so for example -

A B

1 3643 6488

2 5378 9087

3 9631 0092

4 4657 9876

(repeat for several hundred lines)

i need a way to find out if any of these are duplicates. i've tried a few formulas i've found on this site but all i've got back is #NAME? when entering them. also, i'm using openoffice, not excel. does that matter? thanks.

This is nothing special. There's a butt-ton of ways.

Here's my article on it.

http://www.officearticles.com/excel/dealing_with_duplicate_records_in_microsoft_excel.htm

Ok, so i have an excel sheet with a number of columns that i need a formula for to calculate another value in another column in the same sheet. Column A is the one which needs the formula. The data in Column A needs to be a string which looks like this:

GCSO00MNI000

And then needs to have additional characters added to the end which are the values found in column C, such as "107". Therefore, column A's value for that row would be GCSO00MNI000107. The same calculation needs to take place for the next row in Column A, completing it's string with the value in the next row in Column C, and so on and so on, for 6500 rows.

How do i do this and not have to retype the needed calculations for all 6500 rows?

thanks in advance.

Hi.I would like to compare two columns in Excel. Columns are as follows:Column A = xyz,123456,abc Column B = 123456I use the following macro to compare columns where the data in both columns is identical. Can anyone advise how to adapt code when the data is as above?Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant Set CompareRange = Range("C72:C102") ' Loop through each cell in the selection and compare it to ' each cell in CompareRange. For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, -1) = "match" Next y Next x End SubThanks in advance for your help.

Hi,The following code uses the same ranges for data as your original post. The data to be tested is the selected range and it is tested against the data in the range C72 to C102You will see that I have been a bit pedantic in some of the code - for example specifically using Value, although it may be the default for a range object.Also I specifically DIM ranges as Range - this makes the IntelliSense work when creating or modifying code.If VBA knows the variable's type, it can list available functions for it.For example change y to Variant, then go to y.TextDelete the period and then insert it - nothing happens.Do the same with y as Range and you will get Text as an immediate option.Sub Find_Matches()

Dim CompareRange As Range

Dim x As Range

Dim y As Range

Set CompareRange = Range("C72:C102")

' Loop through each cell in the selection and

' see if cells contain text in the cells in the CompareRange.

For Each x In Selection

For Each y In CompareRange

If InStr(1, x.Text, y.Text) <> 0 And y.Value <> "" Then

x.Offset(0, -1).Value = "match"

End If

Next y

Next x

End SubRegards

I need to sort columns in Microsoft excel but here is my dilemma I am having. I need them to be sorted based on the first column alphabetically. Which I understand requires just hitting the sort button.

However.. my grid looks something like this

NAME Hours Address

Tom 6 10 B Street

Bob 3 30 Smith Lane

I want to sort by Name alphabetically. But I want the addresses and hours to also sort along with it. So for example when Tom's name is moved below Bob's I want his hours and Address to also move with it. So everything stays organized. How would I do that?

Select all three columns before doing the sort (DATA --> SORT)

Regards,

Rollin

Has anyone ever encountered missing columns in Excel? I received an Excel file from a co-worker and the columns are lettered from A to H, then L, then from T on. I have never seen a spreadsheet with missing columns before. I am having problems printing the spreadsheet because it cuts off after the L column. My guess is that there is a page bread in one of the missing columns. Can anyone help me with this?

Click on the top left box, where the rows and columns meet. That should select everything. Then right click on one of the rows or columns and choose unhide rows/columns. That's it

BTW

:wave:Welcome to TechSpot:wave:

I would like to merge two columns in excel, to create a third column that has alternating cells originating from the first and second column.

To make it a little clearer: I have values in column A and B

In column E, I would like to see the following result:

E1 = value of A1

E2 = Value of B1

E3 = value of A2

E4 = Value of B2

E5 = value of A3

E6 = Value of B3

...

The attachment to this post contains the result I want to obtain.

i have a csv file..

one column is first and last name.. the other is email address..

in order to import these addresses into MSN e-mail, i need to split the name column into seperate first and last name columns..

IE:

name | email

john doe | [email protected]

needs to be

first name | last name | email

john | doe | [email protected]

thanks

These functions might help. LEFT and RIGHT and Concatenate functions in EXCEL. example. =LEFT(a2,10) will take the left 10 characters of cell A2. =Concatenate(A1,B1) combines cells A1 and B1. Hope this helps.

Hi, i have a list of companies in column A1-260. I have another list of companies in B1 to B185. I want to know the companies in coulmn A which are not in coulm B. I've tried the formulas in the forum but I did not get a good output. I'm a novice excel user so please advise, Thanks

Hi,Enter this in C1=IF(ISNA(VLOOKUP(A1,$B$1:$B$185,1,FALSE)),"Not present","Present")This uses VLOOKUP() to search for the value in a cell in Column A, in the range of cells B1 to B185.If the value is not present, the VLOOKUP() formula returns the #NA error value.The ISNA() function returns True if a value is the #NA error, hence ISNA() returns True when the value in column A is not present in column BThe IF() statement then makes one of two choices based on whether the result returned by ISNA() is True or notIf the value is True, the IF() statement returns (in this example) the text "Not present"If VLOOKUP() finds the value, there is no error, ISNA() returns False and IF() returns the other choice, (in this example) the text "Present"When you have entered the formula in cell C1 drag it down to extend it to row 260.Note the $ signs in the formula - they are required to ensure that the ranges the formula refers to, remain correct when it is dragged.Hope this helps.Regards

2 more repliesI would like to do a loopy using Macro.xls

Let say,

1-start at column 13 do sorting ascending

2- find duplicate ( macro FindDup())

3- next column ... until column IV

and her my Macro record modify by me (!!)

Dim y As Integer

Dim rng As Range

For y = 13 To 255

rng = Cells(1, y).Select

Rows("1:3013").Sort Key1:=rng, Order1:=xlAscending, Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

Application.Run "CP.xls!FindDup"

Cells(1, y).Select

Next y

End Sub

I get error :

Run time error 91.(object variable or With block variable not set.

Any help ?? Thanks in advance.

My excel has columns ad infinitum. I only need through column J. How can I get rid of the other columns so that I can print only through J? Thanks to all who know so much and take time with folks like me who know so little.

you should be able to set the print area

what version of excel do you have

I have moved to the office forum

I am having trouble adding the sum of a row across multiple columns. I have the code to add teh sum from a single columns but I can't figure out how to make it so it will add the columns up and place the sum on the first empty row. The number of cells in each row is not the same so I need to be able code for that.

This code is what I use to sum the rows at the end of the column but like I said earlier I need to code it so it will go to the end of the row while adding all cells in the row from multiple columns.

Option Explicit

Sub StatsEachColumn()

Dim LastColumn As Long, LastRow As Long, lngColumn As Long

LastColumn = Cells.Find(What:="*", After:=Range("A1"), _

SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For lngColumn = 1 To LastColumn

LastRow = Cells(Rows.Count, lngColumn).End(xlUp).Row

With Cells(LastRow + 1, lngColumn)

.FormulaR1C1 = "=SUM(R4C:R" & LastRow & "C)"

End With

Next lngColumn

End Sub

Forgot to mention that it will need to loop since there are 4 different rows so far and it will need the ability to work with any added rows

1 more repliesI have a spreadsheet which contains noughts,ones x, H,and A,s. I would like to count the number of x,s in each column but can't make the COUNTD function work can anybody help?Peter

I have a huge spreadsheet which I am trying to analyse but I really need some help! There is one row of data for each company, then separate columns for products. I need one single column so I can use functions like COUNTIF. A simplified example:

Current situation

Company 1 | Product a | Product d | Product f

Company 2 | Product b

Company 3 | Product a | Product f

Desired result

Company 1 | Product a

Company 1 | Product d

Company 1 | Product f

Company 2 | Product b

Company 3 | Product a

Company 3 | Product f

I am SURE there must be a way of doing this, but I am going round and round in circles right now.... thanks for helping!

I have downloaded text that comprises a series of phrases followed by a variable number of references. The phrases are, actually, names given to Jesus in the Old Testament and the references are the Book and verse. The delimiter of the set is a full stop. So we have:Name,Ref1;Ref2 and so on ending with Refn. The reference is abreviated book name (2, 3 or 4 Characters) followed by a space then the chapter number (one or two digits) a colon and then the verse list that might be a one or two digit number followed by a comma and then another two digit number.For example:Angel, Gen 48:16; Ex 23:20,21.Anointed, Psa 2:2.Bright and morning star, Rev 22:16.I eliminated the full stop that ended an abreviation i.e. Gen. to Gen space) and converted text to single column table using the full stop as delimiter. What I want to do is have the first column the book and each reference in its own column eg:Angel ! Gen 48:16 ! Ex 23:20,21Anointed ! Psa 2:2 ! I don't see how to do it in Word so I put it into a single column Excel 2003 spreadsheet. What I need is a formula to split the column.Maybe it has to be in two stages: split into name & references using the comma and then split the references using the semi-colon.My ideal would be to have just two columns: name and a single reference: thusAngel ! Gen 48:16Angel ! Ex 23:20,21but that seems to be asking a bit too much.

I am summarizing an excel sheet if two columns has the values in the conditionExample: Name Status Date are the columns in the excel sheet with valuesmy formula should check the status and then the date and provide me the total count for a specific date.. how can this be done?

Review the Excel Help file for SUMIFClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesI have used row 1 to label columns, but naturally they scroll up with the contents. Is there a way to keep them on top, or is there another way to label columns?

Hello, I have two columns which have names in each cell. I want to compare the 2 columns & would like to find which cells are duplicate in both columns. Any help would be highly appreciated.

From what I understand, you want to get a list of the cells (namesi n ur case) which is common in two different columns. Vlook up should help!

4 more repliesIf you have Excel data on columns A,B and C sorted by column A, is there a simple way of transposing the columns so you can view the columns in the order of B,C,A sorted by column B, without rearranging the columns by dragging them to new locations?

Hi everyone,Can anyone tell me if it is possible to lock rowshorizontally so that if you sort the data in say one of five columns using the A-Z sort function on the toolbar, the data remains lined up horizontally and doesn't go out of sync.Thanks in advance

Select all of the columns, then Data > Sort and follow the prompts. Nothing should 'go out of sync'.

4 more repliesI have a simple budget spreadsheet, with columns C thru N representing months January thru to December.each row after the headings represents an expense item.Some items have a known amount each month, such as Internet charge, deducted from bank or credit card without intervention, others vary as the money is spent, eg Food. I load the known amounts automatically for the year at the start of the year. The other amounts spent are added as the money is spent.I'd like column O, the next column, to be the sum spent to date for each item. Therefore if today's date is in January, the total in column O would be the amount in column C. If today's date is in March, I'd like O to show the total for the columns C, D and E.I can't find a simple formula to do this. I'm prepared to fix a column to show the month number, or even the rightmost column letter, such as E.

More repliesHi all,I have column A with 500 names and column B with 1500 names.The names in column A are all supposed to be in B.I want to filter out the names that are in A but not in B to another Column C. How do I go 'bout this.Thanks

Formula-wise, you could put this Column C and drag it down:=IF(ISERROR(MATCH(A1,$B$1:$B$1500,0)),A1,"")If the value in Ax isn't found in B1:B1500, the value in Ax will be placed in Cx.Since your Not Found values will be spread out within C1:C500, you would then select Column C, do a Copy...Paste Special...Values to remove the formulae and then sort to bring all the values to the top of Column C.Macro-wise, you can use this code. No sorting would be required.Option Explicit

Sub A_Not_In_B()

Dim valueA, nxtRow, valFound

'Prepare for errors when no match is found

On Error GoTo errHandler

'Check values in A1:A500

For Each valueA In Range("A1:A500")

'Use Match function to look for values from A in B1:B1500

valFound = Application.WorksheetFunction.Match(valueA, Range("B1:B1500"), 0)

Next

'If Match throws up an Error because value wasn't found, then increment

'the Row Counter and place the "Not Found" value from A in Column C

errHandler:

nxtRow = nxtRow + 1

Range("C" & nxtRow) = valueA

Err.Clear

Resume Next

End Sub

Dear all,

I have the following problem: I have a column of data several thousands cells long with values (let's call that D1), indexed by another column (let's call that I1). I want to perform a division operation of the values in that column by the values in another column of values (call that D2), which is indexed in the same way by another column (let's call that I2). But I want to do this operation only for equivalent index codes (le's say, divide the vale indexed as I1=0001 in D1 by the value indexed as I2=0001 in D2).

Nevertheless, the index is not necessarily continuous (as example, if D1 has no value for the index I1=1495, this index doesn't exist in I1, but I2=1495 will exist if there is a non-zero value for this item in D2, so this index number and the related value exists in I2 and D2). How a can I order and divide these series using EXCEL (my version is the 2003 one, BTW)? Can I do this writing a logical function like 'IF' in some way?

Best,

Lucio.

Hi Lucio

Let's run through this "by hand" to see if I am understanding you correctly.

You take the first item of data in the column D1, read iit's index number in I1. Then you search the colum I2 for an exact match with this index number and, if there is one you perform the division of the data in D1 by the data in D2.

If that is a correct interpretation then the function you need is VLOOKUP, to quote the Excel help file

" Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table"

There is a good description of how to use it in the Excel help file, complete with examples. You will probably have to arrange your data in the order I2 D2 to make this work.

i want to merge 2 columns of data the 1st is numerical ie product code and second is alpha ie product discription so i can copy into an access database . the stock column in the access database is 1 column but i want to search by product code when invoicing?merge column a and b and then copy to access so product code is first

In C1 enter=A1 & " " & B1 then click in C1, hover the cursor over the bottom right until the cursor turns into a + then hold down the left mouse button and copy the formula down as far as needed.

8 more repliesif I click on the column at the very top it will highlight the column, then I can drag and drop it somewhere, but it overwrites the column where I dropped it. how do I keep it from doing that?

its a CSV file im working with. I have another CSV viewer where I can easily drag and drop columns. but the problem with that one is the first line is not comma seperated, so I have to open it with notepad and remove the first line before I can view it errr...

Create a blank column into which you can drag/drop the target column

letchworth

I'm hoping for a little help on how to align complex data in excel 2007.In my 1st spreadsheet I have a very large list of GeneIDs arranged in column A with several other columns of data (columns B-T). What I would like to do is add an extra column(s) (U-V) which has COG data in. The COG spreadsheet has GeneID and COG data only, but there are additional or missing GeneIDs in this 2nd spread sheet. Additionally there may be more than 1 row with the same GeneID but a different COG. e.g.GeneID COGJW0012 JJW0013 KJW0013 LJW0016 CJW0017 CIs there a way to get the align the GeneIDs so that any differences between spreadsheet 1 and spreadsheet 2 results in the addition or deletion of rows from spreadsheet 2 only (it's important that there are not additional rows in spreadsheet 1) and so that if there is more than 1 COG it is added to the same row, but different column as the GeneID?Any help would be really appreciated as it would save me going through some 20,000 data points.

Hmmm - the easiest way i think for me would be to sort the two spreadsheets - first by GeneID then COG.Once that is done, copy and paste the two GeneID columns into a new blank worksheet, separated by an empty column.Then use this formula in the first empty slot between the two rows: =countif(f:f;a2)f being the two columns the data is in, a2 being the first empty slot. This will compare the two rows, and will let you know if there are matches. If there is a match, the a2 columns will display a "1", if there is no match a "0" will display.. All of the ones with a "1" you can safely delete, and there is a mtach, all of the "0", you can copy over to the worksheet you are planning on using, as there is no match.

3 more replieswant extract umatched items in excel from c & d and place in column e

What do you mean by unmatched items?Do you have items in C that are not in D and items that in D but not in C and you want them all extracted?Do have a list in C with "extra" items in D and you want the extra items extracted?Something else?Please provide some more details and maybe even a sample of your data with the expected output.Before posting any data, click on the following line and read the instruction found via that link.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesI have three columns of information. Column A is 900 part numbers with no prices, Column C is 600 part numbers matched with their respective $ value in Column D. I need a formula to match the part in column A with the like part in column C and find the $ value from Column D and place that $ value in Column B next to its respective part number. Can anyone help me with this? Thanks so much!!

Want to compare two columns and determine one of three things- was there an increase, a decrease or no change with the results able to be "conditionally formatted" to use an icon set for easy reference.Columns are percentages.

For Conditional Formatting, using an Icon SetTry this:With your data like this: Previous month in cell C3 = 97%Current month in cell C8 = 98%Select cell C8Select Home TabSelect Conditional FormattingScroll down to Icon SetSelect More Rules at the bottom of windowFirst, at the bottom, in the box next to Icon Style click and scroll up/down till you find the icon set:Three Arrows ColoredNext, in the two box's under the word TYPEon the right side, change them both to NUMBERSNext, in the two box's under the word VALUEin the center, change them both to the value: =$C$3Next, in the small box to left of the VALUE boxfor the Green Up Arrow, change the symbol to > (Greater Then)Next, in the small box to left of the VALUE boxfor the Yellow Side Arrow, change the symbol to >= (Greater Then or Equals)Click OKYou should now have a Green Up Arrow in cell C8By changing the value in cell C3 you can change which arrow appears in cell C8MIKEhttp://www.skeptic.com/

8 more repliesI have a simple budget spreadsheet, with columns C thru N representing months January thru to December.each row after the headings represents an expense item.Some items have a known amount each month, such as Internet charge, deducted from bank or credit card without intervention, others vary as the money is spent, eg Food. I load the known amounts automatically for the year at the start of the year. The other amounts spent are added as the money is spent.I'd like column O, the next column, to be the sum spent to date for each item. Therefore if today's date is in January, the total in column O would be the amount in column C. If today's date is in March, I'd like O to show the total for the columns C, D and E.I can't find a simple formula to do this. I'm prepared to fix a column to show the month number, or even the rightmost column letter, such as E.

Well, that is 1 hour of my life I'll never get back, but I now have a feel for relative and absolute r1c1 type cell representation, the INDIRECT function, and getting the month number from today's date. You can Google these topics for lots more guidance than I can give here.Formula in Column O=SUM(INDIRECT("RC[-12]:RC[-" & 13-MONTH(TODAY()) & "]",FALSE))Note my local currency is UK pounds. I hope you can see the screen grab and attachment.

4 more repliesI need to create this table in Excel, How do I create table like I attached? How do I divide 2 row into 2 columns. Im using mac 2013 office.

please help thx

Not sure I follow what you need to do, but....

highlight a group of cells in a row, such as 3A, 3B, and 3C.

Then right click and choose "format cells". Then choose the alignment tab. Then choose "merge cells".

That will combine 3A, 3B, and 3C into a single cell.

Is that what you are trying to do?

You can do the same thing with cells in a column--combine them into a single cell.

You can do that with any combination of cells.

You can then type in whatever you need into a cell and align that typed text to either the left, right, or center of the merged cells.

Hi there,I am starting to export into excel from quickbooks. I do this on a daily basis. In my excel worksheet i have a note column and every time I export from quickbooks the column goes away, i need it be an ongoing list. Is there anyway to frreze the column so it stays the same constantly regardless of how many times i export to the same sheet or will it always overwrite it?Please help.Thank Youp.s when i freeze columns with the freeze pane button the column Notes does freeze but when I export it goes away like it never was there. It's kind of what I want but not helping me with what I need.

How are you Exporting your data from Quicken, in what format?What steps do you use when you Import the data to Excel?MIKEhttp://www.skeptic.com/

6 more repliesPlease HELP,

This problem's going way beyond my excel knowledge. and beyond my friend "ASAP utilities"

Can someone please tell me how to merge 2 Column using a key factor " i.e number".

Here I have 4 column's total.

The left 2 have the correct order/ The Right 2 are in the wrong order.

I need to fix that using their common factor/#.

I have attached the file here.

I would be very happy to fix this ASAP.

How do I do it???

Thanks

Brion Dublin

I have made some mods to your sheets as follows

I created a reference table by

extracting the number part from column E using Find looking for a space and then using Mid to select the number - see the cacl for ref table

Then created a reference table tab - to use as a lookup

Then on sheet1 used vlookup to get the column D & E to align with column A

I have 3 columns with numbers in them and would like to compare if the number in the first column is between the two numbers in the remaining two columns. For example, col A:52230810, col B:52230803, col C:52230813, because 52230803(B)<52230810(A)<52230813(C), so I will claim 52230810 TRUE. I used the following formula but somehow all comparison results show "FALSE".IF(AND(D$1>$B2,D$1<$C2),"TRUE","FALSE")D1 is where the "center" number is. B and C columns contain the lower and upper bound numbers. Where did I do wrong? Great many thanks.

I'm confused on a couple of levels. First, you say that the numbers are in Column A, Column B and Column C, then you say that D1 is where the center number is. What happened to Column A?Second, I copied your numbers directly from your post into B2, C2 and D1 and your formula returned TRUE. That tells me that you don't really have the values you posted in your cells. Something must be different...maybe an extra space, so one of the value is text, or something like that. I'd manually enter your numbers into a another cell, say F1 and then use =F1=B2 and see if it returns TRUE as a means to see if your cells contain what you think they do.Finally, when you put quotes around TRUE and FALSE, you are asking the IF function to return the text strings TRUE or FALSE. That is different than leaving off the quotes which will return the logical values TRUE or FALSE.They are not the same thing and depending on what you plan to do with the values returned by the IF, it could make a difference.To see this for yourself, try this:A1: =IF(1=1, "TRUE","FALSE")B1: =IF(1=1, TRUE, FALSE)C1: =A1=B1Both A1 and B1 will contain TRUE, but you will find that C1 will be FALSE. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesHow do you alter column placemens in Excel so that Column 2 changes places with Column 1?

if I understand you correctly, all you need to do is to cut column A (rows are numeric, columns alphabetic), click on the column after column b, and click insert cut cells.

again, that is if I understand you correctly. Please advise if this works.

I am trying to insert a column in my spreadsheet, but I keep getting the following error message:

To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet.

I don't know why I continue to get that message in this particular spreadsheet. I tried copying and pasting in a new workbook, but it still doesn't insert the column.

Does anyone know what could be wrong?

Never mind. I was finally able to insert the column. I still don't know what the problem was, but I tried copying and pasting again and it worked.

I need to compare corresponding cells of two columns in excel and display the smaller of the two values in the corresponding cell of a third column.Any help would be appreciated. Thanks.

Hi,with your two values in A1 and B1, put this formula in C1:=IF(A1<B1,A1,B1)Drag the formula down column C as far as there are values to be compared in columns A and B.You didn't say if you wanted anything different if the values in A and B were the same. If you do, please respond with further information.RegardsPS as the IF() statement is such a basic building block for formulas in Excel, I suggest you read the help file, so that you understand how it works, and how IF() statements can be nested.

3 more repliesI am trying to compare two columns in Excel. Eg:A B1 12 13 34 45 36 52 15 25 54 3I would like to be high light or format thos cells where B < A. I want to apply it in 1400 rows. Can any one help out me?

have you tried conditional formatting?select the first cell in Col.B and open conditional formatting(or it may be best to select a cell in Col. B that you already know is greater than the adjecent cell in Col. A...that way you'll know right away if this works properly)select New RuleSelect the "Use Formula"and try this: =b2>a1then select whatever type of formatting you would likethen enter, ok, apply whatever buttons are left to set the formatthen you can take the Paint Brush tool to copy the new formatting for the above selected cell and apply it to the rest of Col.B

3 more repliesI need to sort columns in a spread sheet which contains customer names and contact information. I need to sort them first by state and then by city. Do I expand the selection or continue with current selection when I select he state column?

More repliesI'm using Excel 2010.

I have a workbook with TWO Tabs (sorry, cannot attach).

First Tab, the data - Second Tab, the form I want generated.

In the first Tab, Column A is a date. Columns B and C are TEXT Data

For the second tab, I want to create formula that reads like this:

If first tab Column A = a specified date, then bring in the information in first Columns B and C.

If the date appears five times, can it bring the information in and create five rows? (or am I restricted to only cell to cell formulas?)

It sounds like I should just do a simple cut/paste sort of thing and not create a difficult formula, but I really need a formula, because ultimately it will be expanded beyond two simple tabs.

I have formulas that has the data setup in rows (input). The next sheet is formulas that are in columns referencing these individual rows (output). When column A's formulas are dragged to column B, the references change as expected but to Sheet1!B1 rather than Sheet1!A2 (back to the rows). I think I 've done this before, but can't for the life of me remember how. Help me please and I'll be your best friend! (hey, it's the best I can do).

thanks,

drew

Select all the cells involved

Right click anywhere on your selection and from the menu that pops up choose Copy

Click in the cell where you want the list to begin then Right click and from the menu choose Paste special you will now see another menu.

At the bottom right hand side of this menu, Select Transpose.

That's it your list will now run across the page instead of down

Hi,

I have data on the semiconductor industry of companies and their partners (alliances) for the years 91-00 and 01-03.

The data is in 6 columns in the following format:

Alliance ID Company Name Company ID Partner ID1 Partner ID2 Partner ID3 Partner ID 4

The above is for 91-00 and 01-03.

The column Alliance ID assigns a unique ID to each alliance. For example, if the company Intel has a partnership with 2 other companies (Partner ID1 and Partner ID2), then this alliance is given an alliance ID XXXXXX. It is important to note that this ID is for a given combination of partners i.e., the same company Intel can have another partnership with some other company (different partner ID1), then this alliance gets a different alliance ID albeit it being for the same focal company Intel.

However, I only have alliance IDs for the 91-00 data and need to generate them for 01-03. In other words, if a given alliance in 91-00 is repeated in 01-03, then I need Excel to automatically assign the 91-00 Alliance ID to the corresponding row in 01-03.

To illustrate,

In 91-00, I can have the following alliance-Intel partnering with Kodak and Hitachi (partner ID1 and partner ID2 respectively). This alliance can be repeated for 01-03 (Intel with Kodak and Hitachi). Then, I want Excel to assign the 91-00 Alliance ID to the empty cell of 01-03 Alliance ID (of the SAME alliance).

Is there a command or macro program that can do this automatically?

Thanks,

Mithuna.

Can you please upload an example of your spreadsheet (without any sensitive data) so we can see what we are dealing with.

Does anyone know a trick to merging columns in Excel?

Someone manually typed an Excel doc for me with about 2,000 names & addresses. However, they typed the first name in Column A, then typed the last name of each person in Column B.

Is there a way to merge the content of Column B into the content of Column A so that you end up with one column containing the content of both?

That way each first and last name would be together in one cell, instead of in side-by-side cells.

That is because I want to merge this new Excel file with an older, larger one in which the first and last names are already together in one column.

Yes, use the CONCATENATE function to join columns A and B. Let's assume you'll put the formula in column C. For row 1, the formula would be =CONCATENATE(A1," ",B1). Now, if you want to convert that to text instead of a formula, you can Copy and then use Paste Special | Values to put the name as text (not formula) in another column, perhaps column D. Now, you can delete columns A, B, and C if they are no longer needed, or perhaps just hide them.

Alternatively, when you Copy and Paste, select column A as the target, thereby overwriting the contents of column A. However, I think you'll find that deleting or hiding columns A, B, and C is the better technique.

This should be a piece of cake, I just know it! But me and excel never see eye to eye. I have a simple document with column a and column b. I want to find the matching ones and create a column C with the ones that are not matched. Can I do that? Without using copy paste for three hours?? I attached the file below.

Thanks!!

How can i get the lesser amount from 2 given columns? Eg: A1 is 25 and B1 is 23. Now i need to get 23 In C1... what should i do..Pls help me.. Thanks

I am having trouble figuring out how to split some data into separate columns. I have a column that holds numeric dates (example: 01/01/04) - I would like to use the text to columns tool to split this into 3 columns (example: column 1 "01", column 2 "01", and column 3 "04"). It looks like it should be able to do it from the example grid, but it is not working. Can anyone give me step by step instructions with which boxes to check etc...?

This should be simple and I thought I had it nailed, but the formula is not working, so I am obviously doing something wrong.I have two adjacent columns. The first column is always populated with a date and the second column may be populated with a date or may be blank. I am trying to determine if one or both dates are greater than 3/1/2008, then the row is "OK"; if neither date (or if the second column is blank) is before 3/1/2008, then that row is out. Here's what I tried to use: =IF(OR(H2>DATEVALUE("03/01/2008"),I2>DATEVALUE("03/01/2008")),"OK""OUT").The formula just does not return the correct result. Thanks for the help.

You are missing a comma between "OK" and "OUT"Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

17 more repliesI am trying to do the following:If the date is before 1/1/2014 in cell I15 I want cell J15 to say "yes". If the date is after 1/1/2014 in cell I15 I want cell J15 to say "no".Then, in another cell, I want if cell I15 is between 1/1/2014 and 1/1/2015 I want the value in G15 to be placed in cell K15.

For the J15 formula, what do you want to return if the date in I15 is equal to 1/1/2014?The same question holds for the formula in K15: What if the date equals one of the 2 dates that you are checking for?BTW, the solution will be based on the IF function. Have you read anything on the IF function in the Excel Help files or via a Goolge search? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesexcel insists on changing the year I enter (such as 2005) to the current year (2011) after I have specifically formatted the cells in the date format I selected. For example, I enter 5/2005 and end up with 5/2011. No matter what date format I select from the drop-down, it keeps doing this.

I formatted some cells as Custom m/yyyyWhen I enter 5/2005 it displays 5/2005 and shows 05/01/2005 in the fomula bar.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more repliesHiCould you please let me know how can I add up the totals if when one the column contains dates? SUM( ( Sheet1!A2:A7 = "in" ) * ( Sheet1!B2:B7 ="Jan-10") * Sheet1!C2:C7 ) it return 0... ideally would be to get the total from c column if A column = in, and B column = Jan feb and march. is it possible?

B2:B7 ="Jan-10"Is it January 10th 2010 orJanuary 2010?MIKEhttp://www.skeptic.com/

9 more repliesI want to put hourly rates of $6 alongside weekdays and $7 for weekends.I did this it doesn't work:=IF(A2=weekday, 6, 7)Help?

The WEEKDAY function must refer to a cell that contains a date.Assuming your system is set up such that Sunday is weekday 1 and Saturday is weekday 7, this should work:=IF(AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7),6,7)For any WEEKDAY not equal to 1 or 7, the function will return 6.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesProbably a simple solution to a fairly simple question but it's had me stumped for ages.In the first column i typed in a date (26-03-08)what i then want to do is drag this date down and for it to increase in value by weeks ie 02-04-08, 09-04-08 etc. Can it be done, or have i got to go through and type it all out (which would have been quicker now with the time i've spent mucking around).Many thanksNos

Providing the column is formatted as Date then enter the first two dates, select both cells and drag down.

2 more repliesHi,Please help - I'm sure this has already been asked and answerred but I'm really struggling to find the answer I understand.In Excel - I'm trying to get a column of dates to add up and tell me how many date = Jan, how many = Feb and so on. Example. Dates column is formatted like 01-Jan-10. If there are 15 entries of Jan then I want the system to report back in another cell 15, if Feb had 24 enteries then 24 reports back and so on throughout the year.Thanks for any help! I'm sure I'm going to kick myself when I find out the answer - but thanks anyway!

Hello,

What a great help this forum is.

I know nothing at all about VBA, yet managed to get a macro in excel that allows me to send the "due date" emails that works almost perfectly for my needs. I based my macro on the code found here https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/

But i'm still missing one function in this macro for it to be perfect for my needs. I want it to send only one email to the "area manager" specifying all the items (serial number and location) that follow my due-date rule (instead of one email per item). I want it to send a list of items that are due this month for example.

Is it possible?

I am having problems working on a spreadsheet of dates and am looking for help. I am creating a file to show membership ages and senority in our area. Here is my setup.The first column: "Members name". Second column: "Date of birth". Third column: DOB converted to automatically updated "Age" by Year and Month with "Datedif". Fourth column: "Hire date". Fifth column: Hire date automatically converted to "Length of Senority" by Year and Month with "datedif".I want to get the totals and averages of the Age and Senority columns. I think I have the averages worked out by using =AVERAGE(B2:B49) and then converting that with =DATEDIF(B53,TODAY(),"Y")&" Yr, "&DATEDIF(B53,TODAY(),"YM")&" M". But I am not having any luck with the totals.Can anyone help?

It is rather difficult to visualise this.Could you upload the file (with the members names removed) to a site like click here and provide a link to download it.

6 more repliesI have a strange problem I hope someone can shed some light on

I want to make a new column of months from a date

I have a detached with column A as a normal date format 01/12/2006 DD/MMM/YYYY

I then in column B use the month function - so = month(A1)

it displays 12 - and if I go onto the Fx to see the expression helper / wizard

I get told that i get a number returned 1-12 here 1 = Jan and 12 =dec

so that i format the =month(a1) into a MMM-YY format and

get Jan-1900 - I'm cool with the 1900 as i have not specified YYYY but why do i get Jan returned instead of Dec

But what I'm after and maybe a simpler way is to have a dropdown on my pivot table of MM-YYYY from my date field rather than DD-MMM-YYYY

so I can choose and show month data

I'm sure I did this in the past just by using format - MMM-YYYY on a date field and it worked in the picot table OK

any help appreciated

Does Excel (version 9.0) have a feature which will allow me to compute various day/month/year time lapses? For instance can I compute the number of months between Oct. 1, 2003 and September 31, 2013? Or if I want to determine what day it is 25 years from today? I can do this on my handheld, but would like to design an Excel formula for it. Any help would be appreciated.

Use the datedif function

i.e. =DATEDIF(Date1,Date2,"Interval")

Where

Date1 is Start Date

Date2 is the end date

Interval is one of the following: You must use the inverted commas

"m" Months

The number of complete months between Date1 and Date2.

"d" Days

The number of days between Date1 and Date2.

"y" Years The number of complete years between Date1 and Date2.

"ym" Months Excluding Year

The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.

"yd" Days Excluding Years

The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.

"md" Days Excluding Months And Years

The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.

how do i write a formula for this. if priority high add 2 days to date raised, if priority low add 14 days to date raised?

What do you mean by "date raised"?How will Excel know what is a High priority and what is Low?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more repliesI am trying to change some dates in a column in excel 2007. I want the date to read the month and year but the year that I need is 2007 and the cell wants to automatically change to 2008. How can I prevent this from happening?

Thanks for the help

puterputter

Number of working hours between two dates. Working hrs 6:00 AM to 8:00 PM. Including Saturday, excluding Sunday and Holidays

More repliesForgotten how to format dates in excel so they accumulate down the column.e.g. has to be like this down the column1st January 1950, 1st February 1951, 1st March 1950 .... 1st January 2006 help much appreciated. Durko

Should it be 1st March 1952? If so:Tools > Add-ins, make sure Analysis Toolpack is ticked.With the first date in A1 enter in A2=EDATE(A1,13)and copy down as far as needed. Select Column A, Format > Cells and set to display as you wish.

4 more repliesIn an excel spreadsheet, I have two colums, each with a date in it. I want the third column to yield the greater of these two dates. For example, column one has 11/10/09, column two has 12/15/10, I want column three to show 12/15/10 as the greater of these two dates. Thanks.

Try this:If 11/10/09 is in cell A1andIf 12/15/10 is in cell B1in cell C1 enter the formula: =IF(A1>B1,A1,B1)MIKEhttp://www.skeptic.com/

4 more repliesHey

I have imported data from an outside database and it brought in the dates seperated. The information is in columns and the day, month (which is in text), and year each have their own cell. There are hundreds of dates. Due to space considerations I need each date to only take one cell. Is there any way to do this without having to do it by hand?

Not sure which version of Excel you are using, but am assuming 2007. I will also mention what to do if you are using 2003 or earlier

Create a blank worksheet and do the following so that you can see how the following formula works and then how to convert it as a value rather than a formula

In A1 type 24

In B1 type June

In C1 type 1990

In D1 type =DATEVALUE(CONCATENATE(A1,B1,C1))

Assuming you have similar data in the Columns A, B and C, copy the formula in D1 down column D until you have a formula for each row of data in A, B, C

Select all the formulas in column D and click on Home Ribbon tab ans click on the Copy Icon to place it in the clipboard. 2003 and earlier Edit, Copy

Whilst those cells are still selected click on Home ribbon tab and click on the little down arrow just under the Paste button and select paste values. 2003 and earlier Edit, paste Special, Values OK

Your data in column D will now be as a date (not a formula) which in the background is treated as a number, which then allows you to do calculations on the dates.

Now select columns A, B and C and delete those columns

You are now left with column A and the dates in single cells.

Hope that helps

How do I write an If/Then statement in excel to change data to red if it's 6 months old? I have one field for the current date, B2, and a column of dates, B6-B40, that I want to know if the dates are older than 6 mo from today's date. If they are older then 6 months from today's date, I want the dates in column B and their corresponding data in column A to turn red. Any ideas? Thanks in advance.

See if this works for you:This is for Excel 20071) Select your cell or Range of Cells: B6-B402) On the ribbon click Conditional Formatting3) Click on New Rules, it?s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =DATEDIF(G3,TODAY(),"M")>6=DATEDIF(B6,TODAY(),"M")>66) Click on the Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKThis will highlight the cells that are GREATER THAN Six months.For Six Month OR GREATER use:=DATEDIF(G3,TODAY(),"M")>=6=DATEDIF(B6,TODAY(),"M")>=6EDITED, did change the cells from my test sheet.MIKEhttp://www.skeptic.com/

3 more repliesHi,I am looking to write If statement that if I write a date into cell A2, then A1 will display "Good" if its within 4 years since the date in A2, or A1 will display "Expiring Soon" if its between 4-5 years after A2, or A1 will display "Expired" if its 5 years after the date in A2. I have never used If statements with dates before so I don't know where to start and any help will be greatly appreciated.Thanks,

re: "if its within 4 years since the date in A2"By its I assume you mean today's date. If so...Take a look at the EDATE function. EDATE returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date).e.g. For your 5 year (60 months) expiration criteria, this should work.=IF(EDATE(A2,60)<TODAY(),"Expired","")Basically what this says is "If I add 60 months to the date in A2 and the result is less than today's date, then the date in A2 must be more than 5 years ago."The "inverse" of that is to subtract 60 months from today's date to get the same result:=IF(EDATE(TODAY(),-60)>A2,"Expired","")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

3 more repliesThis is multi-part question on how to create different formulas on the attached worksheet.

#1 - I would like to count the number of Start Dates IF the Start Date is blank or greater than today, but only IF there is a date in the Requisition Date column. This would be to indicate the total number of open positions as of today.

#2 - I would like to count the number of cells in column A that are greater than or equal to 17 IF the Start Date is blank or greater than today, but only IF there is a date in the Requisition Date column. This would indicate total number of open positions that have a grade lever of 17 or above.

Hi,

Need Urgent help. Need a formula, explained in detail in attachment and trying to explain here

Column A (Inv Value) Column B (Col.Date) week1 (14/4/2012 - 18/4/2012)

4500 1/5/2012 0 (formula)

225000 23/4/2012 0

55393 17/4/2012 55393 (formula)

* Column A and B are Data field from where we will take data.

* Column C and D are the fields where i need to put formula

**** If column C date range is in Column B date, then put the value of Column A

I can split Column c in two cells with start date and end date if needed (then merge remaining cells)

Appreciate your help and support on urgent bases to solve my issue. < removed email address - etaf moderator >

Quick question.

Lets say I have a date.

11/01

Is there a formula that would return and IF statement?

For example

I am looking to all the dates between 12/15 and 01/15

I have tried to create a formula using the IF statement, but cant only get the first half.

=IF(A1<=12/15,"TRUE","FALSE")

But how do I get the second half for it to check if if the date is less then 01/15? every time I try I get an error.

Howdy. If I understand correctly. Be sure to check year, since the year will be in the cell whether it displays or not.

=IF(AND(A1<=12/15,A1>01/15),"TRUE","FALSE")