IAn if statement for excel ro recognies a cel with a time in

[sdelacey]

Hello All

Can anybody help.I am trying to create a formula that will look at a cell in the next column and if it contains a time . copy the said time into this cell.

a formuka i tried modifing was

=if(mid(B2,8,3)="end",B2)

But I can't get it to work.

Cheers Steth

 

[SkipVought]

Steth,

Realize that TIME values are NUMBERS between 0 & 1, cuz, the UNITS of the Time Value is DAYS. So TIME is a fraction of a day.

You can put .5 into a cell and the FORMAT the cell as TIME and you'll have 12:00 PM.

What are you attempting to do in your spreadsheet?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[sdelacey]

I have a time in B2 the is 12:8 and in C2 I have a word, iIn D2 I have another time eg, 14:56.

`I a, trying to write a formula that will check the cell and if it contains a time , copy it into B1 and the D1.

I tried chaging the format of the cell , but as you say this will change it into a number. so when I tried =IF(mid(B2,3,1)":",B2,0) but this wont work because it is a a decemal number.

Because of this I was wondering if a TIME fuction can be used, so if the cell as a date and tinme format this result will be then copiee into a cell.

Steph

 

[SkipVought]

BTW, you can't COPY with formulas.

Then look at the Cell worksheet function "format" with return values of
[tt]
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"
[/tt]
[tt]
B1: = if(and(cell("format",B2)>="D7",cell("format",B2)<="D9"),B2,"")
[/tt]


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[xlbo]

This should give you an idea:
=IF(LEFT(CELL("format",A1),1)="D","DATE/TIME","OTHER")

Where the cell you are checking is A1

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question