Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 87
Default Time formula returns night time by mistake

A-------------B--------------------C
9/25/08 17:13 9/25/2008 5:13 PDT
9/29/08 10:32 9/29/2008 10:32 PDT
9/29/08 15:12 9/29/2008 15:12 PDT
9/22/08 16:50 9/22/2008 4:50 PDT

I have been using the formula =VALUE(MID($C6,(FIND(" ",$C6,1))+1,FIND("
",$C6,1)-4)) in column B but it returns the wrong time of day. When it has to
calculate 05:13 for example it returned 17:13. Must be an easy fix and
would appreciate your help.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Time formula returns night time by mistake

Try

=TEXT(VALUE(MID($C1,(FIND(" ",$C1,1))+1,FIND(" ",$C1,1)-4)),"hh:mm")

Mike

"ferde" wrote:

A-------------B--------------------C
9/25/08 17:13 9/25/2008 5:13 PDT
9/29/08 10:32 9/29/2008 10:32 PDT
9/29/08 15:12 9/29/2008 15:12 PDT
9/22/08 16:50 9/22/2008 4:50 PDT

I have been using the formula =VALUE(MID($C6,(FIND(" ",$C6,1))+1,FIND("
",$C6,1)-4)) in column B but it returns the wrong time of day. When it has to
calculate 05:13 for example it returned 17:13. Must be an easy fix and
would appreciate your help.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 87
Default Time formula returns night time by mistake


I copied the your formula into C1 but B1 still returns 17:13....am I missing
something?


B------------------C
17:13 9/25/2008 5:13 PDT


"Mike H" wrote:

Try

=TEXT(VALUE(MID($C1,(FIND(" ",$C1,1))+1,FIND(" ",$C1,1)-4)),"hh:mm")

Mike

"ferde" wrote:

A-------------B--------------------C
9/25/08 17:13 9/25/2008 5:13 PDT
9/29/08 10:32 9/29/2008 10:32 PDT
9/29/08 15:12 9/29/2008 15:12 PDT
9/22/08 16:50 9/22/2008 4:50 PDT

I have been using the formula =VALUE(MID($C6,(FIND(" ",$C6,1))+1,FIND("
",$C6,1)-4)) in column B but it returns the wrong time of day. When it has to
calculate 05:13 for example it returned 17:13. Must be an easy fix and
would appreciate your help.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 87
Default Time formula returns night time by mistake

oops I meant I copied your formula into B1

"ferde" wrote:


I copied the your formula into C1 but B1 still returns 17:13....am I missing
something?


B------------------C
17:13 9/25/2008 5:13 PDT


"Mike H" wrote:

Try

=TEXT(VALUE(MID($C1,(FIND(" ",$C1,1))+1,FIND(" ",$C1,1)-4)),"hh:mm")

Mike

"ferde" wrote:

A-------------B--------------------C
9/25/08 17:13 9/25/2008 5:13 PDT
9/29/08 10:32 9/29/2008 10:32 PDT
9/29/08 15:12 9/29/2008 15:12 PDT
9/22/08 16:50 9/22/2008 4:50 PDT

I have been using the formula =VALUE(MID($C6,(FIND(" ",$C6,1))+1,FIND("
",$C6,1)-4)) in column B but it returns the wrong time of day. When it has to
calculate 05:13 for example it returned 17:13. Must be an easy fix and
would appreciate your help.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Time formula returns night time by mistake

Sorry,

I had an elderly moment

Try this instead

MID(C1,FIND(" ",C1,1)+1,(FIND(" ",C1,FIND(" ",C1,1)+1)-FIND(" ",C1,1)))

Mike

"ferde" wrote:

oops I meant I copied your formula into B1

"ferde" wrote:


I copied the your formula into C1 but B1 still returns 17:13....am I missing
something?


B------------------C
17:13 9/25/2008 5:13 PDT


"Mike H" wrote:

Try

=TEXT(VALUE(MID($C1,(FIND(" ",$C1,1))+1,FIND(" ",$C1,1)-4)),"hh:mm")

Mike

"ferde" wrote:

A-------------B--------------------C
9/25/08 17:13 9/25/2008 5:13 PDT
9/29/08 10:32 9/29/2008 10:32 PDT
9/29/08 15:12 9/29/2008 15:12 PDT
9/22/08 16:50 9/22/2008 4:50 PDT

I have been using the formula =VALUE(MID($C6,(FIND(" ",$C6,1))+1,FIND("
",$C6,1)-4)) in column B but it returns the wrong time of day. When it has to
calculate 05:13 for example it returned 17:13. Must be an easy fix and
would appreciate your help.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 87
Default Time formula returns night time by mistake

YES ,YES, YES

Thank you so much

"Mike H" wrote:

Sorry,

I had an elderly moment

Try this instead

MID(C1,FIND(" ",C1,1)+1,(FIND(" ",C1,FIND(" ",C1,1)+1)-FIND(" ",C1,1)))

Mike

"ferde" wrote:

oops I meant I copied your formula into B1

"ferde" wrote:


I copied the your formula into C1 but B1 still returns 17:13....am I missing
something?


B------------------C
17:13 9/25/2008 5:13 PDT


"Mike H" wrote:

Try

=TEXT(VALUE(MID($C1,(FIND(" ",$C1,1))+1,FIND(" ",$C1,1)-4)),"hh:mm")

Mike

"ferde" wrote:

A-------------B--------------------C
9/25/08 17:13 9/25/2008 5:13 PDT
9/29/08 10:32 9/29/2008 10:32 PDT
9/29/08 15:12 9/29/2008 15:12 PDT
9/22/08 16:50 9/22/2008 4:50 PDT

I have been using the formula =VALUE(MID($C6,(FIND(" ",$C6,1))+1,FIND("
",$C6,1)-4)) in column B but it returns the wrong time of day. When it has to
calculate 05:13 for example it returned 17:13. Must be an easy fix and
would appreciate your help.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Time formula returns night time by mistake

Glad I could help.

As an afterthought if there's no leading zero and you want to display one
modify to this

=MID(C1,FIND(" ",C1,1)+1,(FIND(" ",C1,FIND(" ",C1,1)+1)-FIND("
",C1,1)))+TIME(0,0,0)

format as hh:mm

Mike

"ferde" wrote:

YES ,YES, YES

Thank you so much

"Mike H" wrote:

Sorry,

I had an elderly moment

Try this instead

MID(C1,FIND(" ",C1,1)+1,(FIND(" ",C1,FIND(" ",C1,1)+1)-FIND(" ",C1,1)))

Mike

"ferde" wrote:

oops I meant I copied your formula into B1

"ferde" wrote:


I copied the your formula into C1 but B1 still returns 17:13....am I missing
something?


B------------------C
17:13 9/25/2008 5:13 PDT


"Mike H" wrote:

Try

=TEXT(VALUE(MID($C1,(FIND(" ",$C1,1))+1,FIND(" ",$C1,1)-4)),"hh:mm")

Mike

"ferde" wrote:

A-------------B--------------------C
9/25/08 17:13 9/25/2008 5:13 PDT
9/29/08 10:32 9/29/2008 10:32 PDT
9/29/08 15:12 9/29/2008 15:12 PDT
9/22/08 16:50 9/22/2008 4:50 PDT

I have been using the formula =VALUE(MID($C6,(FIND(" ",$C6,1))+1,FIND("
",$C6,1)-4)) in column B but it returns the wrong time of day. When it has to
calculate 05:13 for example it returned 17:13. Must be an easy fix and
would appreciate your help.

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default Time formula returns night time by mistake

Try this:

=MOD(SUBSTITUTE(C6," PDT",""),1)



"ferde" wrote:

A-------------B--------------------C
9/25/08 17:13 9/25/2008 5:13 PDT
9/29/08 10:32 9/29/2008 10:32 PDT
9/29/08 15:12 9/29/2008 15:12 PDT
9/22/08 16:50 9/22/2008 4:50 PDT

I have been using the formula =VALUE(MID($C6,(FIND(" ",$C6,1))+1,FIND("
",$C6,1)-4)) in column B but it returns the wrong time of day. When it has to
calculate 05:13 for example it returned 17:13. Must be an easy fix and
would appreciate your help.

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
calculating night time joe1999 Excel Discussion (Misc queries) 3 September 27th 08 01:03 PM
how to get "day/night" from a cell of time/hour kbee Excel Worksheet Functions 3 February 13th 08 06:48 AM
calculate finishing time if night shift aren't working Martin B Excel Worksheet Functions 1 June 20th 07 11:48 PM
Formula to find Stop Time from Start Time and Total Minutes Jonathan Bickett Excel Worksheet Functions 5 March 7th 07 06:22 PM
time problem or excel mistake engin Excel Worksheet Functions 1 January 11th 06 11:46 PM


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