| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculating night time | Excel Discussion (Misc queries) | |||
| how to get "day/night" from a cell of time/hour | Excel Worksheet Functions | |||
| calculate finishing time if night shift aren't working | Excel Worksheet Functions | |||
| Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
| time problem or excel mistake | Excel Worksheet Functions | |||
Linear Mode
