+ Reply to Thread
Results 1 to 7 of 7

How to enter custom format for both current date and time in cell?

  1. #1
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    How to enter custom format for both current date and time in cell?

    I know we can enter a date and time into a cell separately. But I'd like to create a custom format that allows me to enter both the date and time stamp concurrently in one cell. However when I select the custom format and create such a format it enters the date correctly but not the time. The time defaults to 12:00 PM even though I'm asking for the custom format to time stamp the current time into that cell using the custom format.

    What am I doing wrong? Is there a way to get a custom formatted cell to input both time and date stamp correctly simultaneously in the same cell or do I have to create a separate cell for the time stamp? Thanks in advance.
    Last edited by DorothyFan1; 10-08-2011 at 08:16 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to enter custom format for both current date and time in cell?

    You can enter the date and time in a cell, as, for example 10/08/2011 14:41

    Then you can format that however you like, e.g.,

    hh:mm dd mmm yyyy

    yyyy-mmdd hh:mm

    mm/dd/yyyy hh:mm
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: How to enter custom format for both current date and time in cell?

    But how do I enter both the time and date in the same cell? Is there a way to do this? In other words I pre-format the cell using the custom format and then I enter the date and it automatically enters a time stamp. When I do this it always time stamps it using the default time of 12:00 PM. But that's not right because Excel should be picking up the serial number based on the internal clock of my computer and time stamping the exact hour/minute/second in that cell.
    Last edited by DorothyFan1; 10-08-2011 at 07:10 PM.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to enter custom format for both current date and time in cell?

    But how do I enter both the time and date in the same cell?
    I don't understand the question; I gave you an example in the first sentence of my post.

    You can also enter the current date and time by selecting a cell and doing Ctrl+; space Ctrl+:

    The Ctrl+; enters the date and the Ctrl+: the time.

    and then I enter the date and it automatically enters a time stamp.
    What automatically enters a time stamp?

  5. #5
    Registered User
    Join Date
    10-08-2011
    Location
    Madison, Wi
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: How to enter custom format for both current date and time in cell?

    It would be possible to put the date in one cell (say cell A1) and the time in another cell (say B1). Then in the cell you want both you can enter =A1&" "&B1. This formula will display whatever is in cell A1 followed by a space followed by whatever is in cell B1

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to enter custom format for both current date and time in cell?

    If you chose to do that, you'd want to add the values to get a serial date/time value, not catenate them to create text that looks like a date/time.

  7. #7
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: How to enter custom format for both current date and time in cell?

    The answer appears to be what was stated in this thread:

    Ctrl + : then spacebar then Ctrl/Shift + ;.

    However, there is a small problem with this solution. When you enter it this way...Excel immediately defaults to military time. At this point the way out is to format the cell using a custom format with the aforementioned h:mm:ss AM/PM option.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1