Date format causing formula failure 2

[leehale]

Hello.
I am trying to separate the time from the end of a string including the date as follows:

08/11/05 08:25:26 I use =RIGHT(A10,LEN(A10)-FIND(" ",A10))
This does not work but if I change the time to 08.25 it does work.
Changing the format of the cell does not work either. The only way it works is if I go to the cell and manually change the : symbol for a . symbol.
I have a lot to do and this would be a massive task.

HELP PLEASE.

Lee

 

[GlennUK]

If this is a proper Excel date and time, then your approach is all wrong.

Look at this FAQ :
Why do Dates and Times seem to be so much trouble? faq68-5827

After reading the FAQ, you need to think about what you are going to be doing with the time part of the date/time in cell A10. Will you be needing to use this as a proper Excel time serial?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[leehale]

Thank you Glenn I will have a read of the FAQ and see if it paves the way for me. One thing I never mentioned was that the information is imported from a separate software system that we use to monitor people presence on site. Not sure id this makes it easier to understand. Anyway reading I will go.

Thanks.

Lee

 

[leehale]

GlennUK.

I have read the FAQ and sorry to say it does not cure my problem. Is there a way that I can copy to actual cells into this screen so that you can better understand my problem? Or possibly an e-mail address that I can send a small sample of the sheet for you to interigate?

Thanks anyway.

Lee

 

[GlennUK]

Is you date a "date" or a string? What is the result when you put =ISNUMBER(A10) in an empty cell in this sheet? If TRUE, then it's a proper Excel date/time serial and not a string. If FALSE then it's a string, and your formula would work ( hence I'm guessing it's a real date/time ).

Also, you still haven't said what you are going to do to the time once you've got it.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[leehale]

GlennUK.
I did that and it says TRUE. I have been asked to check the start and finish times of one of our staff and then calculate the hours. The system that we use is not a real time recording system and the transfer data is a mess. I want to take the actual time only part of the line and then compare it to a separate row and give the variance between required time and actual. Once I can transfer just the time it will be easy to do the rest...

Lee

 

[GlennUK]

To get just the time part of a date/time serial, use a formula like this:

=A10-INT(A10)

formatted as time.

To convert it to a decimal numeric version, multiply the result by 24. Formatted as number instead of time.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[leehale]

That was the miracle that I was waiting for. Thank you very much for your time.

Lee.

 

[SkipVought]

Lee,

The FAQ really DID relate to your problem!


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[leehale]

I see that know. It is just sometimes you don't see the wood for the tree's

That is why we relie on you guy's.

Thanks again.