2 questions - one about dates, the other time
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: 2 questions - one about dates, the other time

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    1. I have a four digit number that needs to be changed into one cell, as hh:mm. I know it has something to do with the HOUR and MINUTE command but I don't know what.

    2. I have a date that I need to show the day of week. I also know that the end of the command is "ddd", but I don't know the rest of it.

    I did both of these a looooong time ago.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First one, I'm not sure about. Do you have, for instance 0213 and 2213 or what? Can you give some examples? How do you tell if it is am or pm?

    Second, just format the Cells, use Custom format, and type dddd in the bar at the right.
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    For the numbers: I have all types of numbers, all in military time, everything from 0001 up to 2359.

    And thank you about the cell formatting. That seems to work.

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    For Q1

    =TIMEVALUE(LEFT(E4,LEN(E4)-2) & ":" & RIGHT(E4,2))

    with the cell formatted as the time format you want.

    Regards,
    Jay

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,748
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-09 17:18, jennfurr wrote:
    For the numbers: I have all types of numbers, all in military time, everything from 0001 up to 2359.

    And thank you about the cell formatting. That seems to work.
    In B2 enter:

    =(LEFT(A2,2)&":"&RIGHT(A2,2))+0

    where A2 houses a value like 2359 (military time).

    Format B2 as time.

    BTW, if you have a date say in E1, you want to see the day in say F1, you can use:

    =TEXT(E1,"ddd")

    Aladin


  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-09 17:25, Aladin Akyurek wrote:
    On 2002-04-09 17:18, jennfurr wrote:
    For the numbers: I have all types of numbers, all in military time, everything from 0001 up to 2359.

    And thank you about the cell formatting. That seems to work.
    In B2 enter:

    =(LEFT(A2,2)&":"&RIGHT(A2,2))+0

    where A2 houses a value like 2359 (military time).

    Format B2 as time.

    BTW, if you have a date say in E1, you want to see the day in say F1, you can use:

    =TEXT(E1,"ddd")

    Aladin

    Hi Aladin,

    Good one. Accomplishes the same thing as mine did, only shorter.

    Unclear how times before 10:00am are listed. If the worksheet holds the leading zero, all is fine. If not, LEFT(A2,2) has to be changed to LEFT(A2,LEN(A2)-2)

    Regards,
    Jay

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,748
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-09 17:29, Jay Petrulis wrote:
    On 2002-04-09 17:25, Aladin Akyurek wrote:
    On 2002-04-09 17:18, jennfurr wrote:
    For the numbers: I have all types of numbers, all in military time, everything from 0001 up to 2359.

    And thank you about the cell formatting. That seems to work.
    In B2 enter:

    =(LEFT(A2,2)&":"&RIGHT(A2,2))+0

    where A2 houses a value like 2359 (military time).

    Format B2 as time.

    BTW, if you have a date say in E1, you want to see the day in say F1, you can use:

    =TEXT(E1,"ddd")

    Aladin

    Hi Aladin,

    Good one. Accomplishes the same thing as mine did, only shorter.

    Unclear how times before 10:00am are listed. If the worksheet holds the leading zero, all is fine. If not, LEFT(A2,2) has to be changed to LEFT(A2,LEN(A2)-2)

    Regards,
    Jay
    Jay,

    The notational logic of the miltary time is such that:

    10:00 am --> 1000
    10:00 pm --> 2200

    I assumed that the military time entries obey the military notational logic.

    Regards,

    Aladin

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    and yes, since I work in a police facility, I have all my time-related cells formatted to show four digits including the leading 0.

    Now the next question if anyone's still reading. How would I translate either the above time formula to show decimal time, or just make another formula to show the time in decimal format?

    [ This Message was edited by: jennfurr on 2002-04-10 07:37 ]

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,748
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-04-10 07:32, jennfurr wrote:
    and yes, since I work in a police facility, I have all my time-related cells formatted to show four digits including the leading 0.

    Now the next question if anyone's still reading. How would I translate either the above time formula to show decimal time, or just make another formula to show the time in decimal format?

    [ This Message was edited by: jennfurr on 2002-04-10 07:37 ]
    =(LEFT(A2,2)&":"&RIGHT(A2,2))*24

    Format the cell of the formula as General.

    Aladin

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com