• 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...

  • 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

ThrottleWorks

Excel Ninja
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.
 
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
 
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.
 
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.
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
 
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
 
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.
Hi,

Maybe something like this?
Please refer to attachment
 

Attachments

  • 24to12format.xlsx
    8.6 KB · Views: 12
Back
Top