Date and Time Issue
- Thread starter NJ786
- Start date
NARAYANK991
Excel Ninja
Hi ,
Can you clarify a bit more ?
Your post there has the following :
19.40 "Axe". - 2 >>
21.00 "fish" >>
22.35 "peacock Feather >>
0.20 "Carton"
0.55 "big fish"
2.15 "hen Feather"
I assume you want to do :
1. Separate the numbers , and the text , into two columns ; column A will contain the numbers , and column B will contain the text.
2. How do you want the numbers to be converted to time values ?
3. What are the -2 and the double arrows >> ? Are they to be retained in column B , along with the other text ?
Narayan
Can you clarify a bit more ?
Your post there has the following :
19.40 "Axe". - 2 >>
21.00 "fish" >>
22.35 "peacock Feather >>
0.20 "Carton"
0.55 "big fish"
2.15 "hen Feather"
I assume you want to do :
1. Separate the numbers , and the text , into two columns ; column A will contain the numbers , and column B will contain the text.
2. How do you want the numbers to be converted to time values ?
3. What are the -2 and the double arrows >> ? Are they to be retained in column B , along with the other text ?
Narayan
HI there
As you must have read the previous post, Luke and Fred did a good job there, But then every other scan we get have different formats, as can be seen in this one where the "-", "2" and the ">>" are junk while scanned.
My issue is to get the numbers into separate cell and then format them as Time values and then sort them accordingly and when the clock strikes 00:00 or 12:00am the date should change....
The formula i have used seems very heavy and cluttered and requires too many steps.
While following the previous post can you please guide and fine tune the formulas.
Appreciated.
As you must have read the previous post, Luke and Fred did a good job there, But then every other scan we get have different formats, as can be seen in this one where the "-", "2" and the ">>" are junk while scanned.
My issue is to get the numbers into separate cell and then format them as Time values and then sort them accordingly and when the clock strikes 00:00 or 12:00am the date should change....
The formula i have used seems very heavy and cluttered and requires too many steps.
While following the previous post can you please guide and fine tune the formulas.
Appreciated.
NARAYANK991
Excel Ninja
Hi ,
Sorry if I am asking a lot of questions. As I understand this , you have a series of values in , let us say column B , which are the time values isolated from the scanned data. The sample data will give the following values : 19.4 21 22.35 0.2 0.55 2.15.
From this we can get the time values as follows , step by step :
=INT(19.4) will give 19 ; 19.4 - INT(19.4) will give 0.4 ; 0.4 * 100 will give 40 ; =TIME(19,40,0) will give 7:40:00 PM.
Thus , to get the time from the isolated numbers is not really difficult.
Changing the date requires some logic ; if you are sure that the numbers will always give times in ascending order , then , whenever the current number is less than the previous time , it implies that we have crossed midnight , and the date can be incremented. Is this what you want ?
Narayan
Sorry if I am asking a lot of questions. As I understand this , you have a series of values in , let us say column B , which are the time values isolated from the scanned data. The sample data will give the following values : 19.4 21 22.35 0.2 0.55 2.15.
From this we can get the time values as follows , step by step :
=INT(19.4) will give 19 ; 19.4 - INT(19.4) will give 0.4 ; 0.4 * 100 will give 40 ; =TIME(19,40,0) will give 7:40:00 PM.
Thus , to get the time from the isolated numbers is not really difficult.
Changing the date requires some logic ; if you are sure that the numbers will always give times in ascending order , then , whenever the current number is less than the previous time , it implies that we have crossed midnight , and the date can be incremented. Is this what you want ?
Narayan
Thanks again
Actually my excel experience is not that good when it gets to logic but at times we are forced to come up with things to get things going and make them work, as can be seen from the formula used for the time conversion as well as the date.
Now would you be nice enough to put that into a formula so that it all works out, and for the date change, can you please look at the following.
A1=Time, B1=Condition to determine Date change, C1=Change date based on value of B
B>> "=IF(A2="","False1",IF(I4=A2,"True1",IF(A2=0,"AfterMID",IF((A4>=A3)+(A3<A2)=2,"Midnight","False"))))"
C>> "=IF(B3="Midnight",C2+1,C2)"
and also are you saying that the formula used in my example is wrong and cannot work out in this scenario.
Please check >>>> "But then again can't get the date changed in column D with the formula posted previously with conditions to detect the midnight as soon as it detects the first "0" (zero) in C it changes the date as it thinks the midnight has passed due to the condition." from previous post.
Appreciated
Actually my excel experience is not that good when it gets to logic but at times we are forced to come up with things to get things going and make them work, as can be seen from the formula used for the time conversion as well as the date.
Now would you be nice enough to put that into a formula so that it all works out, and for the date change, can you please look at the following.
A1=Time, B1=Condition to determine Date change, C1=Change date based on value of B
B>> "=IF(A2="","False1",IF(I4=A2,"True1",IF(A2=0,"AfterMID",IF((A4>=A3)+(A3<A2)=2,"Midnight","False"))))"
C>> "=IF(B3="Midnight",C2+1,C2)"
and also are you saying that the formula used in my example is wrong and cannot work out in this scenario.
Please check >>>> "But then again can't get the date changed in column D with the formula posted previously with conditions to detect the midnight as soon as it detects the first "0" (zero) in C it changes the date as it thinks the midnight has passed due to the condition." from previous post.
Appreciated
NARAYANK991
Excel Ninja
Hi ,
I am not really able to understand your usage of "False1" , "True1" and so on.
However , I would say that the easiest way to change the date on crossing midnight , would be to compare each time with the previous time ; thus , when you compare 21:00 with 19:40 , 21:00 is greater ; similarly , when you compare 22:35 with 21:00 , 22:35 is greater.
But when you compare 00:20 with 22:35 , it is less , which means we have crossed midnight ; this can be used to increment the previous date by 1 to get the current date.
Hereafter , again 00:55 is greater than 00:20 ; so also 02:15 is greater than 00:55. This will continue till there is another midnight crossover.
Is this OK with you ?
Narayan
I am not really able to understand your usage of "False1" , "True1" and so on.
However , I would say that the easiest way to change the date on crossing midnight , would be to compare each time with the previous time ; thus , when you compare 21:00 with 19:40 , 21:00 is greater ; similarly , when you compare 22:35 with 21:00 , 22:35 is greater.
But when you compare 00:20 with 22:35 , it is less , which means we have crossed midnight ; this can be used to increment the previous date by 1 to get the current date.
Hereafter , again 00:55 is greater than 00:20 ; so also 02:15 is greater than 00:55. This will continue till there is another midnight crossover.
Is this OK with you ?
Narayan
Hi Narayan
Yes you are right that logic works fine, except when your time crosses midnight for instance midnight occurs at 00:10 am, now the next time that comes up in the next cell would be something like 00:15 or 00:25, then that logic is not useful as it will calculate those values as greater and hence change the date on any change that is greater than 00:00 and hence forth....
so could you come up with something that can solve that particular issue, my formula actually has no particular logic but it works and it lets me know at what stage the formula is working as the cell / column containing that value is not useful to my requirement but only to differentiate between midnight and other times of the day.
Request: Please can you put your logic into creating a formula that would solve the time issue and the midnight issue.
Appreciated.
Yes you are right that logic works fine, except when your time crosses midnight for instance midnight occurs at 00:10 am, now the next time that comes up in the next cell would be something like 00:15 or 00:25, then that logic is not useful as it will calculate those values as greater and hence change the date on any change that is greater than 00:00 and hence forth....
so could you come up with something that can solve that particular issue, my formula actually has no particular logic but it works and it lets me know at what stage the formula is working as the cell / column containing that value is not useful to my requirement but only to differentiate between midnight and other times of the day.
Request: Please can you put your logic into creating a formula that would solve the time issue and the midnight issue.
Appreciated.
NARAYANK991
Excel Ninja
Hi ,
Is it possible you can upload a sample worksheet , with just the raw data , and post the link here ?
It'll help to get everything just right.
Narayan
Is it possible you can upload a sample worksheet , with just the raw data , and post the link here ?
It'll help to get everything just right.
Narayan
I know I'm stepping in late to this conversation, but here's my shot at helping...
Referring to your posted workbook, if you change the formula in B3 to this:
=RIGHT(TRIM(SUBSTITUTE("0"&LEFT(A3,5),".","",1)),4)
and the formula in C3 to this:
=TIMEVALUE(LEFT(B3,2)&":"&RIGHT(B3,2))
column C will now have a proper time format. Now, it looks like you're trying to keep track of the date as well, using just the times. If the times (now in col C) are the start time/basis for determining if it's a new data, the formula in H4 can be:
=IF(E4<E3,H3+1,H3)
I'm not sure what the purpose of columns D, E, F, I, and J are. Was column J there to help with column H, or was there additional "time flagging" that needed to be done?
Referring to your posted workbook, if you change the formula in B3 to this:
=RIGHT(TRIM(SUBSTITUTE("0"&LEFT(A3,5),".","",1)),4)
and the formula in C3 to this:
=TIMEVALUE(LEFT(B3,2)&":"&RIGHT(B3,2))
column C will now have a proper time format. Now, it looks like you're trying to keep track of the date as well, using just the times. If the times (now in col C) are the start time/basis for determining if it's a new data, the formula in H4 can be:
=IF(E4<E3,H3+1,H3)
I'm not sure what the purpose of columns D, E, F, I, and J are. Was column J there to help with column H, or was there additional "time flagging" that needed to be done?
OH oh!
revenge of the bad data format...
just when we were getting it right this is what happens down the line
5.8 Feature film "Street of Newton House, 1" >> issue when the number of characters for the time is less than 4..... (replace the cell with this data and check)
it outputs like this >>> 58 F with the formula in B "RIGHT(TRIM(SUBSTITUTE("0"&LEFT(A3,5),".","",1)),4)"
hence the time will not calculate as desired and the title and name also gets 1 character short.
Please look into this...
revenge of the bad data format...
just when we were getting it right this is what happens down the line
5.8 Feature film "Street of Newton House, 1" >> issue when the number of characters for the time is less than 4..... (replace the cell with this data and check)
it outputs like this >>> 58 F with the formula in B "RIGHT(TRIM(SUBSTITUTE("0"&LEFT(A3,5),".","",1)),4)"
hence the time will not calculate as desired and the title and name also gets 1 character short.
Please look into this...
Rats...what time is that supposed to be? 5:08 AM?
New formula in B3:
=TIMEVALUE(SUBSTITUTE(LEFT(A3,FIND(" ",A3)-1),".",":"))
New formula requires 2 things:
1. A space separates the time from the text
2. A period separates the hrs from minutes.
No need for col C now, formula in H4 becomes:
=IF(B4<B3,H3+1,H3)
New formula in B3:
=TIMEVALUE(SUBSTITUTE(LEFT(A3,FIND(" ",A3)-1),".",":"))
New formula requires 2 things:
1. A space separates the time from the text
2. A period separates the hrs from minutes.
No need for col C now, formula in H4 becomes:
=IF(B4<B3,H3+1,H3)
It will take the first pair.
How it works:
We're using the MID function, so we need to define what letter to start at, and how many letters we want.
To find the starting letter, we use the FIND function to find the first " mark. Then we add 1, since we don't want to include the quotation mark.
Next, we have to find the second quotation mark. We use a FIND function again, but we use the optional 3rd argument to define a starting number to look at (since we don't want to find the 1st quotation mark again, we use that number +1). This tells us the overall position of 2nd quotation mark.
However, as the MID function wants length of characters to return, we need to subtract the first number we found to get the correct length. Similar to before, the -1 at end is to exclude the 2nd quotation mark.
If there are multiple instances, we could do some tricks using the SUBSTITUTE function. For instance:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A3,"""",REPT(" ",999),2),"""",REPT(" ",999),1),500,1500))
Might work. This formula replaces the 1st and 2nd instance of a quotation mark with a big space. The MID function just takes a nice chunk out of the middle, and trims the extra spaces. If you know the total count of quotation marks (see Hui's recent blog post) you can manipulate this to choose which quotation marks to replace.
How it works:
We're using the MID function, so we need to define what letter to start at, and how many letters we want.
To find the starting letter, we use the FIND function to find the first " mark. Then we add 1, since we don't want to include the quotation mark.
Next, we have to find the second quotation mark. We use a FIND function again, but we use the optional 3rd argument to define a starting number to look at (since we don't want to find the 1st quotation mark again, we use that number +1). This tells us the overall position of 2nd quotation mark.
However, as the MID function wants length of characters to return, we need to subtract the first number we found to get the correct length. Similar to before, the -1 at end is to exclude the 2nd quotation mark.
If there are multiple instances, we could do some tricks using the SUBSTITUTE function. For instance:
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A3,"""",REPT(" ",999),2),"""",REPT(" ",999),1),500,1500))
Might work. This formula replaces the 1st and 2nd instance of a quotation mark with a big space. The MID function just takes a nice chunk out of the middle, and trims the extra spaces. If you know the total count of quotation marks (see Hui's recent blog post) you can manipulate this to choose which quotation marks to replace.
NARAYANK991
Excel Ninja
Hi ,
Yes ; since the ASCII code for the double quote is 34 , " is equivalent to CHAR(34).
=FIND("""",A3) should do the same job as =FIND(CHAR(34),A3)
Narayan
Yes ; since the ASCII code for the double quote is 34 , " is equivalent to CHAR(34).
=FIND("""",A3) should do the same job as =FIND(CHAR(34),A3)
Narayan