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. ### ThrottleWorksExcel Ninja

Messages:
1,717
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. ### NARAYANK991Excel Ninja

Messages:
15,394
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.

Messages:
1,717

4. ### ThrottleWorksExcel Ninja

Messages:
1,717
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. ### NARAYANK991Excel Ninja

Messages:
15,394
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. ### SwapnilMember

Messages:
46
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. ### PCosta87Well-Known Member

Messages:
870
Hi,

Maybe something like this?

#### Attached Files:

• ###### 24to12format.xlsx
File size:
8.6 KB
Views:
9
ThrottleWorks likes this.
8. ### ThrottleWorksExcel Ninja

Messages:
1,717
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.