Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Paul Sexton
 
Posts: n/a
Default Extract the hour from a cell showing the date and time

I have a field that shows Date and time in the same field. That shows like
this

1/3/05 12:06 PM

I need to extract either 13:00 or 13 or 1 PM from this field. Text to
collumns does not work. I need a formula.

Can you help?

Paul
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Extract the hour from a cell showing the date and time

Hi

I don't quite understand why you want 13:00, but as a start, try:
=MOD(A2,1)
This will 'chop off' the date bit, leaving just the time section.

Hope this helps.
Andy.

"Paul Sexton" wrote in message
...
I have a field that shows Date and time in the same field. That shows like
this

1/3/05 12:06 PM

I need to extract either 13:00 or 13 or 1 PM from this field. Text to
collumns does not work. I need a formula.

Can you help?

Paul



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Carl Hilton
 
Posts: n/a
Default Extract the hour from a cell showing the date and time

Why not try HOUR() or HOUR(TIMEVALUE()).

Carl

"Paul Sexton" wrote in message
...
I have a field that shows Date and time in the same field. That shows like
this

1/3/05 12:06 PM

I need to extract either 13:00 or 13 or 1 PM from this field. Text to
collumns does not work. I need a formula.

Can you help?

Paul



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
putting date and time together in 1 cell Robbie Excel Discussion (Misc queries) 2 February 22nd 05 04:03 PM
Combined date time cell to separate date & time components Mark Ada Excel Discussion (Misc queries) 1 December 2nd 04 03:48 AM
Combined date time cell to separate date & time components Mark Ada Excel Discussion (Misc queries) 1 December 2nd 04 01:07 AM
Combined date time cell to separate date & time components Mark Ada Excel Discussion (Misc queries) 1 December 2nd 04 01:07 AM
Combined date time cell to separate date & time components Mark Ada Excel Worksheet Functions 1 December 2nd 04 01:04 AM


All times are GMT +1. The time now is 02:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"