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: Trouble formatting date in excel spreadsheet

Discussion in 'Business Applications' started by MoneyGuy99, Aug 19, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. MoneyGuy99

    MoneyGuy99 Thread Starter

    Joined:
    Apr 13, 2005
    Messages:
    178
    Hi everyone, I was having some trouble with the format of the date in my excel document.

    Basically what i want to do is type in for example todays date (Aug 18), and then when clicking below onto the next row, I would like to see the date come up above as (08/18/2005).

    Instead the problem im getting is the date comes up as (08/18/2018)????

    Ive tried right-clicking the entire column, where the date is being entered, and using "Format Cells" to set the appropriate date format. I have also verifed the date is set at 2005, so far nothing works.

    I welcome any ideas/suggestions???

    Thanx everyone
     
  2. Sponsor

  3. Anne Troy

    Anne Troy Anne

    Joined:
    Feb 14, 1999
    Messages:
    11,744
    What IS the format on the cells?
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Hi Anne :) ; d'ya think this could be a Regional Settings thing? (Regional Options -- Customise -- Date -- Calendar)
     
  5. Anne Troy

    Anne Troy Anne

    Joined:
    Feb 14, 1999
    Messages:
    11,744
    Don't know until we see the format. Sure...could be anything.
     
  6. MoneyGuy99

    MoneyGuy99 Thread Starter

    Joined:
    Apr 13, 2005
    Messages:
    178
    The format settings: I choose "date," and for type i choose "3/14/98." Sorry my mistake the date comes out as 08/18/18, i want it to come out as 08/18/05.

    Also, just to let u know, this problem only occurs on this users computer, and when emailing the document to me, it works just fine, all i do is set the format on the cells, and it works like a charm??? but when sending the document back, it doesnt work for her, i know the cells are set right, and her date on her computer is right as well. Not sure if it has anything to do with the regional settings???
     
  7. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Today's date (19th Aug) is 38583, in Excel-speak.

    Try entering that in a cell, then formatting the cell as you require.

    What do you get?
     
  8. lagunasrfr

    lagunasrfr

    Joined:
    Feb 13, 2005
    Messages:
    415
    To test this solution:

    Format column A as Date, mm/dd/yy

    Place this in cell a1: =DATE("2005","08","18")

    place this in cell a2: =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

    Replicate a2 downward in Column a.

    This will not treat February and the 30 day months properly if your start date (Cell a1) contains a day greater than 28. You could solve that problem with a more complex formula involving "if" statements.
     
  9. MoneyGuy99

    MoneyGuy99 Thread Starter

    Joined:
    Apr 13, 2005
    Messages:
    178
    Hey (lagunasrfr) I tried your solution, it worked!! It was a little bit complicated to trouble-shoot over the phone, but it worked!!! thank you all for your help, really appreciate everyone's helps, thanx again!!
     
  10. lagunasrfr

    lagunasrfr

    Joined:
    Feb 13, 2005
    Messages:
    415
    I'm glad it works, XL Guru. It got me intrigued, so I've been almost obsessed with finding a formula that would work for begin day >28. It involves a whole lot of nested "if"s and that gets pretty confusing.

    I can get rid of my obsession, knowing those days are not a problem.
     
  11. 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...
Similar Threads - Solved Trouble formatting
  1. mt1013
    Replies:
    1
    Views:
    275
Thread Status:
Not open for further replies.

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