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

Hi all

Is there a way(VBA or function/formula) to extract the time from a serial
date in excel?

i.e. date in cell is 38140.7291666667 (6/2/2004 5:30 pm)
and want to extract time only

Thanks in advance

DW


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default extract time from excel serial date

Function

=A1-INT(A1)

VBA

Now - Date

or

Range("A1") - Int(Range("A1"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan W" <Dw6853_AT_msn_DOT_com wrote in message
...
Hi all

Is there a way(VBA or function/formula) to extract the time from a serial
date in excel?

i.e. date in cell is 38140.7291666667 (6/2/2004 5:30 pm)
and want to extract time only

Thanks in advance

DW




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default extract time from excel serial date

Thanks Bob
That did the trick


"Bob Phillips" wrote in message
...
Function

=A1-INT(A1)

VBA

Now - Date

or

Range("A1") - Int(Range("A1"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan W" <Dw6853_AT_msn_DOT_com wrote in message
...
Hi all

Is there a way(VBA or function/formula) to extract the time from a

serial
date in excel?

i.e. date in cell is 38140.7291666667 (6/2/2004 5:30 pm)
and want to extract time only

Thanks in advance

DW






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

On Mon, 7 Jun 2004 12:39:17 -0600, "Dan W" <Dw6853_AT_msn_DOT_com wrote:

Hi all

Is there a way(VBA or function/formula) to extract the time from a serial
date in excel?

i.e. date in cell is 38140.7291666667 (6/2/2004 5:30 pm)
and want to extract time only

Thanks in advance

DW


With the value in A1, =MOD(A1,1) will extract the fractional part, which is
the time.


--ron
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default extract time from excel serial date

Thanks Ron

--

Dan W
(replace _DOT_ and _AT_ with normal email characters "@ . " if mailing
direct)


"Ron Rosenfeld" wrote in message
...
On Mon, 7 Jun 2004 12:39:17 -0600, "Dan W" <Dw6853_AT_msn_DOT_com wrote:

Hi all

Is there a way(VBA or function/formula) to extract the time from a serial
date in excel?

i.e. date in cell is 38140.7291666667 (6/2/2004 5:30 pm)
and want to extract time only

Thanks in advance

DW


With the value in A1, =MOD(A1,1) will extract the fractional part, which

is
the time.


--ron



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
Converting date/time serial values to cumulative time totals... Kevin B Excel Discussion (Misc queries) 4 October 18th 07 05:05 PM
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
How Do I Eliminate the Serial Number for time from Date? Bob Gotti Excel Discussion (Misc queries) 2 January 19th 06 06:24 PM
Format serial date and time Cedric Excel Discussion (Misc queries) 2 December 26th 05 10:06 PM


All times are GMT +1. The time now is 06:27 AM.

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"