Solved: Date Formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

TRG Fred

Thread Starter
Joined
Oct 25, 2007
Messages
6
I’m doing a mail merge and a date that I would like to merge to my letters is found in 2 different columns on my .csv data sheet. Column A has mm/dd and shows “1106” for November 6th and Column B has yy and shows “7” for 2007. The data is formatted as a number – when I format the “1106” to a date it shows “1/10”. Is there a formula to take the data from both cells and format it to show “11/06/2007”? I'm using Excel 2000 and XP.
 
Joined
Aug 30, 2007
Messages
13
A simple solution is to enter =A1 & "/" & B1 in column C1, that will concatenate the values for you. If you have other rows with dates in you can copy and paste the formula or use Excel's autofill feature. Hope this helps,

regards,
Trevor
 
Joined
Jul 25, 2004
Messages
5,458
Hi, welcome to the board!

I disagree with Trevor, I do not believe that will give you the correct results you desire, but rather a concatenated string of your numbers put together instead of an actual Excel recognized date, which is completely different. Instead, use the DATE() function to get that recognized date....

Code:
=DATE(2000+B1,LEFT(A1,2),RIGHT(A1,2))
This assumes that your "1106" is in A1 and your "7" is in B1.

HTH
 
Joined
Aug 7, 2007
Messages
9,028
firefyt's formula will only work if the month and day is always 4 digits. If the date is 506 (for may 6th) that formula will give 2/6/2011 instead of 5/6/2007.
The following will probably work better for ya:

Code:
=DATE(2000+B1,ROUND(A1/100,2),MOD(A1,100))
This will work as long as the date is always 2 digits, ie, may 6th is shown as 506. If it gets entered as 56 it too will fail. While you can check for cases with single digit dates, there is no way to determine the correct date for 111-119 (1/11 or 11/01?) and 121-129 (1/21 or 12/01?). Here's that formula for the curious:
Code:
DATE(2000+B1,ROUND(A1/IF(A1<100,10,100),2),MOD(A1,IF(A1<100,10,100)))
I don't know about excel 2000 date formats (I'm using 2003) but the only format that shows a 4 digit year would display that date as 11/6/2007. If you need it to be 11/06/2007 you'll need to use a custom format to specify always having 2 digit dates, ie m/dd/yyyy or mm/dd/yyyy to always display 2 digit months.

hth

Jerry
 
Joined
Oct 20, 2004
Messages
7,837
I agree with firefytr - use the date function. One thing that he forgot to mention, however, is that after you have done that, highlight and copy the new date-looking data (it still exists only as a formula) and then paste special, values to save the data as dates.
Good point outcaste - since TRG Fred says he can format the monthday cell as a date, it must exist as a number, and Excel drops preceding zeros, so most monthday cells will only be 3 digits (across the year, with only October, November and December possibly having 4 digits). A workaround would be to special format all the monthday column as 0000. which would put a preceding zero in front of all 3 digit numbers, Copy that and paste it into NotePad. Format a column in Excel as text. Copy the data in NotePad and paste it in as text. Use that column instead of the original data.
 
Joined
Jul 25, 2004
Messages
5,458
The reason for my formula, just fyi, was the OP said the format was in "mm/dd" format, which means you will always have a two digit month and a two digit year. So, from the specs, my formula will work everytime. And the kicker? It will be returned as an Excel recognized date. Slurpee is correct with the values though, but the great thing about the Date() function is that static value or not, it is a recognized date. ;)

Yes there are a hundred ways to skin this cat, but having a structured format will save countless hours in the future in maintenance, upkeep and troubleshooting.
 

TRG Fred

Thread Starter
Joined
Oct 25, 2007
Messages
6
Thanks for all the replies. Firefytr DATE() function works for the cells that have 2 characters for the month and day but I have some cells that show “101” for January 1st. All my data has 2 characters for the day – “101” is January 1st and “1001” is October 1st. So the DATE() function does not work for “101”. I don’t understand Slurpee 55 ‘s workaround to add the 0000 to the cells. This all seems incredibly complicated. I’m thankful for the forum…..
 
Joined
Jul 25, 2004
Messages
5,458
Ok. Well if you know the day will always be two digits regardless, I would look at the outcaste's function(s) or you can use ...

Code:
=DATE(2000+B1,LEFT(A1,LEN(A1)-2),RIGHT(A1,2))
 
Joined
Aug 7, 2007
Messages
9,028
As long as the date is always 2 digits, the first formula I gave would work.

Code:
=DATE(2000+B1,ROUND(A1/100,2),MOD(A1,100))
As well as the modified formula firefytr just posted.

I'm not sure, but I think firefytr's formula may be a bit faster -- extracting strings may be less processor intensive than doing the math in my formula. If you have a big bunch of dates to convert it might make a difference.

What slurpee55 is talking about is assigning a custom format to the cells that contain the month and day. To do this, highlight all the cells that will contain the month and days, then right click on them, then left click format cells. On the number tab click on Custom in the category list, then in the Type box type "0000" without the quotes. This tells excel to display a digit for each 0 you type, so to force it to be always 4 digit, use 0000.

If you do that, you can use firefytr's first formula.

HTH

Jerry
 
Joined
Jul 25, 2004
Messages
5,458
I believe you are right Jerry, regarding the speeds. Do you have FastExcel to test? I would wonder which would be the larger culprit though, Round or Mod (I would assume Mod).
 

TRG Fred

Thread Starter
Joined
Oct 25, 2007
Messages
6
Thanks! Firefytr formula works for me!
BTW I've changed my experience level after reading your replies!
 
Joined
Aug 7, 2007
Messages
9,028
firefytr said:
I believe you are right Jerry, regarding the speeds. Do you have FastExcel to test? I would wonder which would be the larger culprit though, Round or Mod (I would assume Mod).
I'd agree that MOD would be the larger culprit. And no, I don't have FastExcel. Looks like a good utility for someone who designs lots of spreadsheets. Being just a hobbyist I'm not likely to add it to my utilities just yet. Maybe when I get a bit more familiar with Excel (I'm more familiar with Quattro Pro, but haven't done anything major with it in several years) I'll get that big consulting contract and can afford it. ;)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top