Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Date formatting; adding a 'time' to the date

    Hi all....I have been playing with the 'date' and 'time' custom formats trying to come up with something that will permit me to enter a date and add a time (eg: Apr 29, 2011 @ 3:00" (or 3:00pm). I know that I can do it manually, but at the moment, I am using the xl add-in called Pop-Up Calendar (a free add-in that allows you to right-click a cell and select 'Insert Date' and it will put the selected date into the cell.....I have been able to use a custom date format that reads [$-409]mmmm dd, yyyy "@" (eg: May 14, 2011 @) but as soon as I edit by adding a time, the date is displayed as 5-14-11 @

    Any ideas as to how I can get the formatting to hold as mmmm dd, yyyy and be able to enter a time? Thanks.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    mmmm dd, yyyy "@" hh:mm
    should work.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Ah ha...it does work....it will require entering the time manually after I use the pop-up calendar to pick a date but that's ok....thanks Rory....ps: and, if I wanted to add some text either after the time, or before the date, (eg: Rm 001, mmmm dd, yyyy @ 9:00am, or, mmm dd, yyyy @ 9am, Rm 001) is there some way to do that and still display the date as January 5, 2011, for example, rather than 01-05-11 ??..??

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If it's fixed text, then just change the format to:
    "Rm 101 " mmmm dd, yyyy "@" hh:mm
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Never easy..)...it's not fixed text...sorry...ps: can you think why am email address would suddenly lose it's hyperlink-ability (if that's the word)? I entered email addresses; they worked; then, w/o explanation, they are no longer 'active' ..??..??...

    I think I figured it out...I was runniing a macro that reformatted the address...the default format is Arial 10...I prefer Verdana 12 and so I made a sorting macro that also changes the font to Verdana 12....this seemed to 'de-hyperlink the addresses (?)....hwoever, when I go to click on one, it brings up an old email address from that same list....any ideas how to purge that old one?
    Last edited by dmcnab; 2011-04-14 at 10:47.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If it's not the same text then you'd need to use a formula in another cell (or code)
    No - unless you deleted hyperlinks or edited the cells.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    I have double checked the format and it shows style as Hyperlink.....the email addresses have the standard appearance of an email address...I have a list of names, numbers and email addresses (including my own) on 1 sheet.....on sheet 2, I use a drop-down list to select a name and it then populates the 2 adjacent cells with the number and email address....it will still do that (and the email addresses are active on sheet 1) but on sheet 2, all email addresses are active but only to my email address..??....and the Style is set for hyperlink

  8. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    OK...I think I have it fixed....I had to re-copy the formulas all the way down but that seems to have reactivated the email addresses.,...not sure why, but it seesm to be working as it should.

Posting Permissions

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