if & isnumber (& vlookup)

[kelvin_9]

i am trying to write a formula to return my value with request:
#1 ) if cell B2 value is "full", return with "anytime" in C2
#2 ) if cell B3 value is "0", return with ""(leave it blank) in C3
#3 ) if cell B4 value is contain "0:00", return with the original value in C4

actually i want to use if+vlookup, or any better idea?
in #3 , ":00" is a time value in 00:00-00:00 with format general, but i can't return successfully if i use search(timevalue(hh:mm-hh:mm) function

=IF(ISNUMBER(SEARCH("full",availability!E9)),"anytime","")&IF(ISNUMBER(SEARCH("0",availability!E9)),"","")&IF(ISNUMBER(SEARCH(":00",availability!E9)),availability!E9,"")

[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]request[/TD]
[TD]return[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]full[/TD]
[TD]anytime[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]0[/TD]
[TD]leave it blank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]08:00-20:00[/TD]
[TD]original value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


thanks

 

[Bo_Ry]

Maybe try at C2

=IF(B2=0,"",IF(B2="full","anytime",B2))

 

[kelvin_9]

what if i add vlookup to avoid any sequence error?

 

[MB110]

I would think about the "request" field and it's entry possibilities. John's request is not clear. If you were to use data validation and limit the entries to a list of options then VLOOKUP would work well. If John's request is a start and stop time, and you need to calculate the difference, then I would consider another table design. Clearly define "request" and it's parameters and then you will better understand your data structure needs.

Proper input makes for easy output!

HTH,
MB110

 

[kelvin_9]

I would think about the "request" field and it's entry possibilities. John's request is not clear. If you were to use data validation and limit the entries to a list of options then VLOOKUP would work well. If John's request is a start and stop time, and you need to calculate the difference, then I would consider another table design. Clearly define "request" and it's parameters and then you will better understand your data structure needs.

Proper input makes for easy output!

HTH,
MB110

thanks for your reply.
actually i have 3 worksheets in my work book
1) sheet_schedule - a database scheduled by a team with start and end time for everyone, assuming 3 peoples, Apple, Peter & John
2) sheet_availability - everyone's availability, assuming with above request, 'full' for apple means, she is available all time. '0' for Peter, he is not good at that day. 'specific time' for John, he is fine with the said time.
3) sheet_result - with above table, in column C, i may need a if+isnumber+vlookup formula? to return the one i want like:
=IFERROR((IF(VLOOKUP($A1,availability!$A:$E,5,FALSE)="full","anytime",IF(VLOOKUP($A1,availability!$A:$E,5,FALSE)="0","unavailable"))),"wrong")
this can applied to C2 & C3, but not for C4, so i may try to use isnumber&seacrh to find ":00"

question is
everytime may subject to change the first one in column A, so i'd prefer vlookup to make sure that it's prefect.

*wrong = after updating column A, if the person is undefined, return wrong. i will look into it and find the reason behind.

thanks MB110

 

[MB110]

If the value in B4 format is general and typed in like a string of text and you want to know if the difference between those times then you will have to deconstruct the string using left, right, mid and len functions. Way too complicated if you ask me.

If you can simply use two cells to capture start and stop times you can easily check it to see if the difference is greater than zero. You could even show the result as a string if you want by concatenating the fields.

C4 = 08:00 formatted as hh:mm
D4 = 20:00 formatted as hh:mm

E4=IF((D4-C4)>0,TEXT(C4,"hh:mm")&"-"&TEXT(D4,"hh:mm"),"")

Therefore E4 formula result = 08:00-20:00 unless the condition is false and then its blank.

Sorry if I don't follow your problem but it sure seems like time values entered as a string is at the root of this.

HTH,
MB110

 

[kelvin_9]

If the value in B4 format is general and typed in like a string of text and you want to know if the difference between those times then you will have to deconstruct the string using left, right, mid and len functions. Way too complicated if you ask me.

If you can simply use two cells to capture start and stop times you can easily check it to see if the difference is greater than zero. You could even show the result as a string if you want by concatenating the fields.

C4 = 08:00 formatted as hh:mm
D4 = 20:00 formatted as hh:mm

E4=IF((D4-C4)>0,TEXT(C4,"hh:mm")&"-"&TEXT(D4,"hh:mm"),"")

Therefore E4 formula result = 08:00-20:00 unless the condition is false and then its blank.

Sorry if I don't follow your problem but it sure seems like time values entered as a string is at the root of this.

HTH,
MB110

08:00-20:00 in B4 is under one cell, not separate, thus i want to return the original value by searching :00 if have instead of counting it in between.

thanks MB110

 

[MB110]

If the value in B4 contains :00 then return the value of B4. What if it does not contain :00? Do you want the result to be blank?

 

[MB110]

Try this formula in C4...

=IF(B4="full","anytime",IF(B4=0,"",IF(OR(MID(B4,3,3)=":00",MID(B4,9,3)=":00"),B4,"")))

 

[kelvin_9]

Try this formula in C4...

=IF(B4="full","anytime",IF(B4=0,"",IF(OR(MID(B4,3,3)=":00",MID(B4,9,3)=":00"),B4,"")))

thanks MB110, i will try later after work.
to tackle any change of my team's availability, can i simply add ":15/:30 even :45" in to your formula?
=IF(B4="full","anytime",IF(B4=0,"",IF(OR(MID(B4,3,3)=":00",MID(B4,9,3)=":00"),MID(B4,3,3)=":15",MID(B4,9,3)=":15"),MID(B4,3,3)=":30",MID(B4,9,3)=":30"),MID(B4,3,3)=":45",MID(B4,9,3)=":45"),B4,""))) ?

if so, how about vlookup to make sure no change will be made if sequence in column A changed?
eg. this time A2-Apple, A3-Peter, A4-John / next time A2-Peter, A3-John, A4-Apple... and so on.

thanks again MB110