TimeValue Function

[GN0001]

hello to all,

I have 200,000 rows, I have dates and times as this: 12/1/2011 6:12:45 AM

The cell itself doesn't show the time, but when I look into the bar, I can see the time.

I need to pull out the time.

I used right function and the TimeValue function as follow.

=Right (Cell, 10)

it gave a serial number.

I used the TimeValue function: It gave a serial number,gives me #value.

What is the problem?

How should I use the TimeValue?

Thank you for the help.

G

 

[NARAYANK991]

Hi ,


Use the following formula :


=VALUE(RIGHT(Cell Address,11))


and format the cell as Time. You should see the time displayed.


Narayan

 

[GN0001]

Thanks a lot, it worked for me at me, I need to try it at work tomorrow. Is there anyway that I can pull only hour part and pm and am part of the cell? I used Right and left function, since we don't know if the hour is two digits or one digit only, we don't know how many we should use in the left function. I think there should be a better way. Many, Many thanks, G

 

[NARAYANK991]

Hi ,


To extract the hour part :


=INT(VALUE(RIGHT(Cell Address,11))*24)


To extract the AM / PM part :


1. Use an intermediate cell , say Z5 , with the formula : = INT(Cell Address) ; this gives the value of the date at midnight.


2. Now use the following formula : =IF(Cell Address>Z5+"12:00:00","PM","AM")


Narayan

 

[GN0001]

Naryan,

When I Use this part: INT(VALUE(RIGHT(Cell Address,11))*24), it doesn't give me the hour part, but it turns the the time to 12:00:00 am. I will work on that more and get back to you tomorrow. Thank you so much for the help. G

 

[GN0001]

NaryanK,

When I Use this part: INT(VALUE(RIGHT(Cell Address,11))*24), it doesn't give me the hour part, but it turns the the time to 12:00:00 am. I will work on that more and get back to you tomorrow. Thank you so much for the help. G

 

[GN0001]

Naryank,


Thank you very much for your time and help.

it seems that my response is posted two times.

This is what I have done.

I used:

1-VALUE(RIGHT(Cell Address,11))

It worked for me and I stripped off date of time.

2-I have times as 9:23:45 AM

3-I used the text function to get the hour part of the function, I didn't understand why you have multiplied by 24, and multiplying by 24, it gives me 12:00:00 AM, that is why I used text function.

Now I have the hour part of (9:23:45 AM) in a separate column.

4- I need to extract AM or PM. I used the instruction you have given me, but it doesn't pull the correct PM. I do have 09 PM, which is not correct. Any idea?


IF(Cell Address>Z5+"12:00:00","PM","AM")

This never goes to right part of If statement, because the value in the cell can never be bigger than 24. However I get the values as 09 pm or 13 pm which 9 pm is not correct. Any suggestion? or tips?


I am very grateful to your help. Thank you very much.

 

[NARAYANK991]

Hi ,


From your first post , I understood that you have dates and times ; can you confirm whether you have only times in your data , or is it a combination of dates and times ?


I have the following data in cell K11 : 1/1/2011 7:59:59 AM


Using the following formula : =INT(VALUE(RIGHT(K11,11))*24)


I get 7 , which is the hours part.


Using the formula =INT(K11) gives me 1/1/2011 12:00:00 AM , which is midnight. Adding 12:00:00 to this gives me the changeover time , for changing from AM to PM.


Using the IF statement returns the correct value of AM or PM depending on what the value in K11 is.


Can you post a few items of data from your worksheet ?


Narayan

 

[GN0001]

I did the formula=INT(K11), while I had 12:00:00 pm in the cell. I didn't put the date in that cell. Let me work on that and get back with more data. Thank you for all your precious help. G

 

[GN0001]

Naryan,

I did the first part,

It means that I used: =Int(Value(Right(cell reference, 11))*24) and I pulled the time part of that, however I don't get why you have multiplied by 24

===============================================================

Then for the second part I entered:

12:00:00 pm and when I did Int(12:00:00 pm), it gives me 12:00:00 am. Anyway, I entered 12:00:00 pm (say in cell c3)l to be able to do this function = if ($c13> c3 + "12:00:00", "pm", "am") to extract pm or am.

It worked for all the rows, not some of them though, and because I have a bunch of rows it is hard to catch where the function is not calculated correctly.

I got this:

9:09:58 am am

9:08:13 am am

9:04:22 am pm

9:09:08 am pm

last two rows are not correct, because they need to return am not pm. Do you think what the problem is? How to solve it?

Also in this formula ($c13> c3 + "12:00:00", "pm", "am") is "12:00:00" used as a value, I mean 12 hours? or concatenated to the value of C13? Or added to the value of C13?

Please advise me. I really appreciate you time.

Many thanks,

G

 

[NARAYANK991]

Hi ,


1. Dates and times , in Excel , are just numbers ; thus 1-Jan-1900 is 1 , 2-Jan-1900 is 2 and so on. Since the date changes at midnight , these numbers all refer to midnight. Thus 1-Jan-2012 is the same as 1-Jan-2012 00:00:00 is the same as 1-Jan-2012 12:00:00 AM is the same as 40909 !


2. Since each date is an increase of 1 , which is also 24 hours , the times between one midnight and the next are all fractions ; thus , since 1-Jan-2012 12:00:00 AM is 40909 , and 2-Jan-2012 12:00:00 AM is 40910 , the 24 hours between these two dates are represented by decimals between 40909 and 40910 , such as 40909.25 , 40909.34 , 40909.71 and so on.


3. Thus , taking the INT value of any date , basically reduces it to the date changeover time of midnight ; if you have a time like 1-Jan-2012 09:34:56 AM , taking the INT of this will return the value 40909 which is 1-Jan-2012 12:00:00 AM.


4. When you take a formula such as C3+"12:00:00" , what you are doing is mathematical addition ; you are adding 12 hours to whatever value is in C3 ; if C3 contains midnight , then this addition will return noon , which is the time changeover from AM to PM.


5. In the cases where you are getting the wrong values of AM / PM , just check by entering in any unused cell the same value as the date + time which is returning the wrong value ; in the example you have given , if the time is 09:08:08 am , which is returning "PM" , enter the same value i.e. 09:08:08 AM ( even "am" is OK ) in say C13 ; use the same formulae viz. :


=INT(C13) should return 12:00:00 AM


=IF(C13>INT(C13)+"12:00:00","PM","AM") should return AM


Narayan

 

[GN0001]

Thank you for all,

both functions worked for me and with your explanation I understood how the date value and functions are working . But as I said before, the functions give lots of values which is not correct, per your instruction, I hand typed the data in cells, then the function returned the correct value. But here, this discussion comes up: Once you have 200,000 rows, then what would you do to clean each cell to get the correct value? I assume the cells whose output are not correct has some characters, How to find out what they are and how to remove them?

Thank you for the help and your time.

G

 

[NARAYANK991]

Hi ,


Unless you post some samples which give error results , it is difficult to say what the problem can be ; can you post some sample data ?


Showing up the wrong data is possible in a couple of ways - one way is to use Conditional Formatting ; another is to use a helper column to return a 0 or a 1 , 0 if the data is erroneous , 1 if it is valid ; once this helper column is set up , you can filter on 0 in this column to look at all the invalid data and correct it if possible ; correcting is possible once you know all the different kinds of errors in your data.


Narayan

 

[SirJB7]

Hi,GGGGG!

To pull out the time from a cell containing a date-time value you can do this:

A1: cell with date-time value

B1: =INT(A1)

C1: =A1-INT(A1) ... or ... =A1-B1

Then you'll have the date part in B1 and the hour part in C1.

If you have any cell with an invalid date-time content, you'll get an error value in B1/C1.

Regards!