1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to derive hours from date and time value

Discussion in 'Ask an Excel Question' started by ThrottleWorks, Jan 12, 2017.

  1. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,569
    Hi,

    I have values as 10/8/12 6:28:30 PM in a column. I need to derive hours value from this value. I am using this formula '=TEXT(A1, "hh")' to get the hours. However my problem is this formula gives result in 24 hour format.

    For example. if the value is '04/01/1900 23:39:03' I get result as 23. I want it as 11 not 23. Can anyone please help me in this.
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,314
    Hi Sachin ,

    If A1 has a date + time value , then the following formula will give the hours in 12 hour clock time.

    =MOD(MOD(A1,1)*24, 12)

    What should be the result at noon and midnight ?

    Narayan
    ThrottleWorks likes this.
  3. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,569
    Hi @NARAYANK991 sir, thanks a lot for the help. Please give me some time. To be honest, I forgot about this scenario.

    Have a nice day ahead. :)
  4. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,569
    Yes, @NARAYANK991 sir, for 02/01/1900 00:03:43, result is coming as '0.0619333333452232', am trying if '=MOD(MOD(A1,1)*24, 12)' result is less than 1 then 12 else populate original result.

    Will get back to you.
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,314
    Hi Sachin ,

    Midnight is 0 , and will therefore give a result of 0.

    Noon is 0.5 , and will again give a result of 0 , because of multiplication by 24 and then taking a MOD of that using 12.

    How to differentiate between these two values of 0 is the issue.

    Narayan
    ThrottleWorks likes this.
  6. Swapnil

    Swapnil Member

    Messages:
    45
    Hi Sachin,

    May be below formula can address your problem , Considering Cell A1 has the Date+Time value , Midnight will give result as Zero while Noon will give result as 12

    =IF(TEXT(A1,"hh")+0<13,(TEXT(A1,"hh")+0),(TEXT(A1,"hh")-12))

    I hope this helps
    ThrottleWorks likes this.
  7. PCosta87

    PCosta87 Well-Known Member

    Messages:
    658
    Hi,

    Maybe something like this?
    Please refer to attachment

    Attached Files:

    ThrottleWorks likes this.
  8. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,569
    Hi @PCosta87 sir, thanks a lot for the help. I guess it is working fine.

    Hi @Swapnil , thanks a lot for the help, however facing the same issue.

    Hi @NARAYANK991 sir, I guess, @PCosta87 solution is working for me.

    Have a nice day ahead. :)
  9. PCosta87

    PCosta87 Well-Known Member

    Messages:
    658
    I'm glad it helped ;)
    ThrottleWorks likes this.

Share This Page