if & isnumber (& vlookup)

kelvin_9

Board Regular
Joined
Mar 6, 2015
Messages
248
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,"")

namerequestreturn
Applefullanytime
Peter0leave it blank
John08:00-20:00original value

<tbody>
</tbody>


thanks
 
Last edited:

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Bo_Ry

Board Regular
Joined
Oct 27, 2018
Messages
112
Office Version
  1. 365
Platform
  1. Windows
Maybe try at C2

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

MB110

New Member
Joined
Mar 24, 2019
Messages
29
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

Board Regular
Joined
Mar 6, 2015
Messages
248

ADVERTISEMENT

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
 
Last edited:

MB110

New Member
Joined
Mar 24, 2019
Messages
29
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
 
Last edited:

kelvin_9

Board Regular
Joined
Mar 6, 2015
Messages
248

ADVERTISEMENT

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

New Member
Joined
Mar 24, 2019
Messages
29
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

New Member
Joined
Mar 24, 2019
Messages
29
Try this formula in C4...
Code:
=IF(B4="full","anytime",IF(B4=0,"",IF(OR(MID(B4,3,3)=":00",MID(B4,9,3)=":00"),B4,"")))
 

kelvin_9

Board Regular
Joined
Mar 6, 2015
Messages
248
Try this formula in C4...
Code:
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,992
Messages
5,526,109
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top