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.

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.

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.

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.

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.

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.

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.

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.