Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 557
Default Extract time from date

Hi all, I have formated cell A1 in "dd\mm\yyyy hh:mm" and value
appearing is "26/08/2009 10:00". I need some formula in cell B1 to
extract just the time bit from cell A1 value. I know I can copy cell
A1 value into cell B1 and change the format into "hh:mm" and i'll get
what i want but i dont want to do it that way. I need some thing like
"RIGHT(A1,5)". I tried this but its not working. Please can any
friend can help.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Extract time from date

Use this...

=MOD(A1,1)

and format the cell as Time.

--
Rick (MVP - Excel)


"K" wrote in message
...
Hi all, I have formated cell A1 in "dd\mm\yyyy hh:mm" and value
appearing is "26/08/2009 10:00". I need some formula in cell B1 to
extract just the time bit from cell A1 value. I know I can copy cell
A1 value into cell B1 and change the format into "hh:mm" and i'll get
what i want but i dont want to do it that way. I need some thing like
"RIGHT(A1,5)". I tried this but its not working. Please can any
friend can help.


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,276
Default Extract time from date

Hi,
=text(a1,"hh:mm")

if this helps please click yes thanks

"K" wrote:

Hi all, I have formated cell A1 in "dd\mm\yyyy hh:mm" and value
appearing is "26/08/2009 10:00". I need some formula in cell B1 to
extract just the time bit from cell A1 value. I know I can copy cell
A1 value into cell B1 and change the format into "hh:mm" and i'll get
what i want but i dont want to do it that way. I need some thing like
"RIGHT(A1,5)". I tried this but its not working. Please can any
friend can help.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Extract time from date

Just so the OP knows, your formula as written, returns text, not a number.
If the OP needs a number, then he should use this...

=--TEXT(A1,"hh:mm")

--
Rick (MVP - Excel)


"Eduardo" wrote in message
...
Hi,
=text(a1,"hh:mm")

if this helps please click yes thanks

"K" wrote:

Hi all, I have formated cell A1 in "dd\mm\yyyy hh:mm" and value
appearing is "26/08/2009 10:00". I need some formula in cell B1 to
extract just the time bit from cell A1 value. I know I can copy cell
A1 value into cell B1 and change the format into "hh:mm" and i'll get
what i want but i dont want to do it that way. I need some thing like
"RIGHT(A1,5)". I tried this but its not working. Please can any
friend can help.


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 557
Default Extract time from date

thanks Eduardo
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
How to extract time and date Hemanth Excel Discussion (Misc queries) 2 June 27th 07 03:00 PM
extract date between a given time frame B New Users to Excel 1 April 3rd 07 09:18 PM
Extract the hour from a cell showing the date and time Paul Sexton Excel Worksheet Functions 2 February 27th 06 07:15 PM
Calculating days & time left from start date/time to end date/time marie Excel Worksheet Functions 7 December 7th 05 03:36 PM
extract time from excel serial date Dan W[_2_] Excel Programming 4 June 8th 04 06:52 PM


All times are GMT +1. The time now is 08:13 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"