Computer Support Forum

dates in columns on excel

Question: dates in columns on excel

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

Relevance 100%
Preferred Solution: dates in columns on excel

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.)

Answer: dates in columns on excel

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

2 more replies
Relevance 66.83%

Hi,

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!
 

Answer:Sorting dates in two columns

you could setup a helper column and use
=if(C2="", D2, C2)
and then you can sort based on that new column
 

1 more replies
Relevance 66.42%

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?

Answer:How can I auto-populate dates in Excel based on other dates?

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 replies
Relevance 65.6%

I 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!!

Answer:adding columns based on dates in another colu

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/

12 more replies
Relevance 65.6%

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
 

Answer:Solved: Printing 4 Columns from 2 columns in excel

Welcome to the board.

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

1 more replies
Relevance 57.4%

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?

Answer:converting excel dates in Excel 2003

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 replies
Relevance 54.94%
Question: Excel columns?

Hi 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
 

Answer:Excel columns?

Hi gazmysta,

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

BR
Hew
 

2 more replies
Relevance 54.94%
Question: Excel Columns

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
 

Answer:Excel Columns

9 more replies
Relevance 54.94%
Question: Excel Columns

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
 

Answer:Excel Columns

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
 

2 more replies
Relevance 54.94%
Question: Excel columns

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?

Answer:Excel columns

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

2 more replies
Relevance 54.53%
Question: Excel Dates

Eldest 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

Answer:Excel Dates

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 replies
Relevance 54.53%
Relevance 54.53%
Question: excel dates

Filename: 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
 

Answer:excel dates

What is your problem and/or question?
 

2 more replies
Relevance 54.53%
Question: Excel Dates

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.
 

Answer:Excel Dates

12 more replies
Relevance 54.53%
Question: Excel dates

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?

Answer:Excel dates

Hopefully this guide will help.

2 more replies
Relevance 54.53%
Question: Dates and Excel

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!

Answer:Dates and Excel

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 replies
Relevance 54.53%

I'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' ?!?!?

Answer:Dates in Excel VBA (again !) ...

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 replies
Relevance 54.53%
Question: Excel and dates

I 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??

Answer:Excel and dates

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

24 more replies
Relevance 54.53%
Question: excel dates tip

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

Answer:excel dates tip

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 replies
Relevance 54.53%
Question: Excel and dates

Hi 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

Answer:Excel and dates

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 replies
Relevance 54.53%

I'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

Answer:Excel - between two dates

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

10 more replies
Relevance 54.53%

I 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
 

Answer:Need Excel HELP with Dates

7 more replies
Relevance 54.12%

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.
 

Answer:I need more columns EXCEL 2000/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
 

1 more replies
Relevance 54.12%

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.
 

Answer:Excel compare columns

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
 

2 more replies
Relevance 54.12%

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.
 

Answer:Formula in excel columns

9 more replies
Relevance 54.12%

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.

Answer:Compare two columns in Excel

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

4 more replies
Relevance 54.12%

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?
 

Answer:Sorting Columns in Excel

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

Regards,
Rollin
 

2 more replies
Relevance 54.12%

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?
 

Answer:Missing Columns in Excel

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:
 

1 more replies
Relevance 54.12%

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.
 

Answer:Merge columns in Excel

6 more replies
Relevance 54.12%

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
 

Answer:splitting excel columns..

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.
 

3 more replies
Relevance 54.12%

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

Answer:Need help with Comparing Columns in Excel

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 replies
Relevance 54.12%

I 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.
 

Answer:Excel columns loopy

12 more replies
Relevance 54.12%

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.

Answer:deleting columns from excel

you should be able to set the print area
what version of excel do you have

I have moved to the office forum

3 more replies
Relevance 54.12%

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

Answer:Excel VBA: Need sum of a row across Multiple Columns

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 replies
Relevance 54.12%

I 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

Answer:counting in excel columns

Try=COUNTIF(A:A,"x")

2 more replies
Relevance 54.12%

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!
 

Answer:Excel multiple columns to one

7 more replies
Relevance 54.12%

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.

Answer:Splitting columns in Excel

Have you looked hereclick here

2 more replies
Relevance 54.12%

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?

Answer:counting two columns in excel

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

2 more replies
Relevance 54.12%

I 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?

Answer:Labelling columns in Excel

Position cursor in Row 2. Window/Freeze panes.

10 more replies
Relevance 54.12%

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.

Answer:Comparing Columns in Excel

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 replies
Relevance 54.12%

If 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?
 

Answer:Transposing Excel Columns

10 more replies
Relevance 54.12%

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

Answer:Sorting columns in Excel

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

4 more replies
Relevance 54.12%

I 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 replies
Relevance 54.12%

Hi 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

Answer:Comparing columns in excel

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

5 more replies
Relevance 54.12%

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.
 

Answer:Ordering columns in EXCEL

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.
 

3 more replies
Relevance 54.12%

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

Answer:excel how to merge columns

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 replies
Relevance 54.12%

if 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...
 

Answer:moving columns in excel?

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

letchworth
 

3 more replies
Relevance 54.12%

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.

Answer:MS excel line up 2 columns

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 replies
Relevance 54.12%

want extract umatched items in excel from c & d and place in column e

Answer:Need to compare excel columns c & d

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 replies
Relevance 54.12%

I 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!!
 

Answer:Excel Columns merge

7 more replies
Relevance 54.12%

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.

Answer:Comparing two columns in Excel.

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 replies
Relevance 54.12%

I 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.

Answer:Adding columns in Excel

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 replies
Relevance 54.12%

I 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

Answer:how to divide row into 2 columns in excel

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.

2 more replies
Relevance 54.12%

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.

Answer:Freezing Columns in Excel

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 replies
Relevance 54.12%

Please 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
 

Answer:help merging columns in Excel 03 !!!

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
 

3 more replies
Relevance 54.12%

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.

Answer:compare 3 columns in EXCEL

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 replies
Relevance 54.12%

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

Answer:Transposing Excel Columns

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.
 

1 more replies
Relevance 54.12%

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?
 

Answer:Excel....inserting columns

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.
 

2 more replies
Relevance 54.12%

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.

Answer:compare 2 columns in excel

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 replies
Relevance 54.12%

I 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?

Answer:I am trying to compare two columns in Excel.

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 replies
Relevance 54.12%

I 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 replies
Relevance 54.12%

I'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.
 

Answer:Excel 2010 - IF=, THEN... for COLUMNS

7 more replies
Relevance 54.12%

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
 

Answer:Excel Columns to Rows

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
 

3 more replies
Relevance 54.12%

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.
 

Answer:Matching Columns in Excel

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

1 more replies
Relevance 54.12%

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.
 

Answer:merging columns in Excel?

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.
 

6 more replies
Relevance 54.12%

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!!
 

Answer:Excel-matching up columns

12 more replies
Relevance 54.12%

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

Answer:Excel how to find the less from 2 columns

=MIN(A1:B1)

3 more replies
Relevance 54.12%

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...?
 

Answer:Text To Columns in Excel

8 more replies
Relevance 54.12%

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.

Answer:nested if excel with or and dates

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 replies
Relevance 54.12%

I 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.

Answer:If Statement in Excel with Dates

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 replies
Relevance 54.12%

excel 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.

Answer:why does excel change my dates

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 replies
Relevance 54.12%

HiCould 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?

Answer:Excel sum formula with dates

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

9 more replies
Relevance 54.12%

I 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?

Answer:EXCEL If else statement with dates

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 replies
Relevance 54.12%

Probably 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

Answer:creating dates in excel

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

2 more replies
Relevance 54.12%

Hi,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!

Answer:Counting dates in MS Excel

What kind of entries are you talking about?

12 more replies
Relevance 54.12%

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?
 

More replies
Relevance 54.12%

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?

Answer:Working with dates in Excel

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 replies
Relevance 54.12%

I 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
 

Answer:Excel: Dates: Month

7 more replies
Relevance 54.12%

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.
 

Answer:Computing Dates in Excel

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.
 

1 more replies
Relevance 54.12%

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?

Answer:Excel formula to add dates

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 replies
Relevance 54.12%

I 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
 

Answer:Dates in Excel 2007

6 more replies
Relevance 54.12%

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

More replies
Relevance 54.12%

Forgotten 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

Answer:Excel and Formatting Dates

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 replies
Relevance 54.12%

In 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.

Answer:excel if then statements with dates

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 replies
Relevance 54.12%

Hey
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?
 

Answer:Solved: Dates in Excel

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
 

3 more replies
Relevance 54.12%

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.

Answer:if then statements with dates in excel

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 replies
Relevance 54.12%
Relevance 54.12%

Hi,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,

Answer:How to If Statements Using Dates in Excel?

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 replies
Relevance 54.12%

This 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.
 

Answer:Excel-Counting with dates

7 more replies
Relevance 54.12%

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 >
 

Answer:Excel Formula - Dates

11 more replies
Relevance 54.12%

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.
 

Answer:Solved: Excel Dates

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")
 

3 more replies