Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel Time/date and phone numbers

    Hey all, I did a quick scan through and couldn't find anything to actually suit my issue.

    I am running Office 2007

    Prob 1) When I have a column I wish to run Phone numbers in, I want the 0417, or +61 prefixes in.
    "Help" tells me to go to "format cells" "special" and select "Phone numbers"
    I do not have "Phone numbers" in "Special", or anywhere else.

    Prob 2) Sometimes when i am adding dates in a column, I can only put in "Aug-13"
    Next line I might had a ful date as in 14-Aug-13
    I want teh damned column to leav in what i put in it..
    I dont want it to change the 14-Aug-13 to 2013/08/14 Or to anything else...
    HOW on earth do I delete ALL formatting of a cell, or tell Excel to ignore whatever it has been told to do by someone who thinks he knows more about what I want then I do please????

    Prob 3) Time.. Excel does the same thing with time..
    I have a spreedsheet where I wish to record as follows...
    01 hours: 34 minutes: 26 seconds
    Excel continues to tell me that number in AM as in 01:34:26 etc..
    SO.. when I have 65 entries, and try to add up all the durations, I end up with 12:33:24PM...

    MAYBE something is missing with the formatting capabilities of my Office 2007..
    I am not a Computer geek ... just an old caveman trying to record what I want...

    Cheers:
    kio

  2. #2
    3 Star Lounger
    Join Date
    Apr 2012
    Posts
    240
    Thanks
    3
    Thanked 24 Times in 24 Posts
    All of these problems relate to formatting of cells.

    1. To enter phone numbers, right click the range you want, select "Format Cells", near the bottom of the "number" tab you should see Special - then choose format.

    2. To set date format, right click your date range, choose format, Number Tab, Date, and either select one of the pre-defined date types, or create your own (under "Custom" number format), or try formatting the range as Text, and it will just leave your date as you entered it, but be warned, you won't be able to sort by date, they'll sort alphabetically.

    3. To add times together, right click a range, choose format cells, Numbers, Custom, and enter h:mm:ss, then use sum to add them up.

    Hope this helps, good luck!

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    1) some of the locales do not have a phone number default in XL. You may have to create your own under "CUSTOM". What format do you want exactly.

    2) If you enter a date in a cell with a general format, XL tries to convert it to a date and then re-format that cell to that format. If you want you can reformat the cell after the fact to a different format. If you want the entry to be entered exactly as you enter it and not have any conversion, prefix the entry with a single quote ('). But beware that this is then text and is not a date. Dates are numbers (the number of days since Dec 31, 1999) so any date value must be converted by excel to convert to the number, and must be formatted to display as a date.

    3) Times are also numbers and must be converted. Excel keeps track of times (as well as dates) in units of the number of days. So the integer part of a number is the date and the decimal the parts of a day. [0.25 = 6 hrs, 0.5 = 12 hours, 0.75 = 18 hours]. Because of this the time functions in excel can not display hours past 24. If you are using times and will have hours >24 and want to display them that way, do not use "Time of day" like hh:mm:ss, use elapsed times [h]:mm:ss.

    If you need additional clarification, please let us know.

    Steve

  4. #4
    Lounger
    Join Date
    May 2007
    Location
    desertland, Western Australia, Australia
    Posts
    33
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks to Both you fella:
    1) Ok.. So, I tried Formatting to text, and ended up with a column of weird numbers.. so, OBVIOUSLY I have to Format BEFORE I enter the details..
    No problems there, I will have to go back, and re-enter the details as required...

    2) I have to try to get my head around this information - once again, not a problem.. AGAIN, I have to format the column before I enter details..
    Fixable problem. (I think.. from the little tests I ust did)

    3) the [h]:mm:ss might do it.. I will play around, and see what happens with the end results..

    In Summary.. I do not seem to be able to create any "Special" or "Custom" format at all.

    Spo, I will play with what you have suggested, and IF I don't succeed, I will get back to you.
    Cheers:

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I do not seem to be able to create any "Special" or "Custom" format at all.
    Is it just this workbook or all workbooks in Excel? What steps do you use to try and create a custom format?

    Steve

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Is this Kio ex CSI?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •