1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Date Formula

Discussion in 'Business Applications' started by TRG Fred, Nov 6, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. TRG Fred

    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.
     
  2. trevorshaw

    trevorshaw

    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
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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
     
  4. TheOutcaste

    TheOutcaste

    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
     
  5. slurpee55

    slurpee55

    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.
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Ain't Excel great? ;)
     
  8. TRG Fred

    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…..
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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))
     
  10. TheOutcaste

    TheOutcaste

    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
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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).
     
  12. TRG Fred

    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!
     
  13. TheOutcaste

    TheOutcaste

    Joined:
    Aug 7, 2007
    Messages:
    9,028
    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. ;)
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hehe, I hear ya there. So, whereabouts in Oregon are ya? I'm in Eastern Oregon. :)
     
  15. TheOutcaste

    TheOutcaste

    Joined:
    Aug 7, 2007
    Messages:
    9,028
    In Western Oregon, east side of Portland, so i guess we could call it Eastern Portland:D
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/648669

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice