LinkBack URL
About LinkBacks
Hello
Trying to write theese 3 conditions with if and i need some help to do it.
Thanks in advance for any help.
A2>"06:30" et A2<="14:30";"Jour"
A2>"14:30" et A2<="22:30";"Soir"
A2>"22:30" et A2<="06:30";"Nuit"
Hello
Trying to write theese 3 conditions with if and i need some help to do it.
Thanks in advance for any help.
A2>"06:30" et A2<="14:30";"Jour"
A2>"14:30" et A2<="22:30";"Soir"
A2>"22:30" et A2<="06:30";"Nuit"
this is doable if you convert the times into numbers. AND, don't put times or numbers into quotes because that changes them to text which doesn't work with greater or less than or equal to.
I believe this will work for you...
=IF(AND(A2>0.2708;A2<=0.6042);"Jour";IF(AND(A2>0.6042;A2<=0.9375);"Soir";IF(OR(A2<=0.2708;A2>0.9375);"Nuit";"")))
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hello Sir
it works, but i have 2 questions for you.
how to convert the time to number
when A2 is empty, the formula display "Nuit". How to make it display blank when A2 is nothing?
Thank you
well, for question 1, you don't have to convert your time to numbers, I did it in the formula. If you have times in the cells, times are simple representations of numbers, so noon (12:00) looks like a time in a cell but if you convert from a time to a number it will look like 0.50.
question 2, change the formula to this instead...
=IF(A2="";"";IF(AND(A2>0.2708;A2<=0.6042);"Jour";IF(AND(A2>0.6042;A2<=0.9375);"Soir";IF(OR(A2<=0.2708;A2>0.9375);"Nuit"))))
Try this formula:=IF(A2="","",IF(OR(MOD(A2,1)>TIME(22,30,1),MOD(A2,1)<=TIME(6,30,1)),"Nuit",IF(MOD(A2,1)>TIME(14,30,1),"Soir","Jour")))
Last edited by josephteh; 03-27-2022 at 12:07 AM.
tha's work.
thanks a lot!
Or try this:
=IF(A2="","",LOOKUP(A2*86400,{0,23401,52201,81001},{"Nuit","Jour","Soir","Nuit"}))
Hello
As mentionned, your code is working great, but i have another request which is!
1. if B2 is not empty, do the code
2. if B2 is empty, switch to A2 value to execute the code.
For better understanding, i add a screen shot.
Thanks!
This is not code, it's a formula.
Try this:
=IF(AND(A2="",B2=""),"",LOOKUP(IF(B2<>"",B2,A2)*86400,{0,23401,52201,81001},{"Nuit","Jour","Soir","Nuit"})))
Semi-colons may be needed for your locale:
=IF(AND(A2="";B2="");"";LOOKUP(IF(B2<>"";B2;A2)*86400;{0;23401;52201;81001};{"Nuit";"Jour";"Soir";"Nuit"})))
Last edited by AliGW; 03-27-2022 at 11:30 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hello
Doesn't work for me. when i erase B2, the formula doesn't take A2 value. insted, the cell shows nuit.
Thanks!
Well, it should!
Attach a sample workbook, then we can see what's happening.
Hello
Really appreciate your help!
Is working fine now.
Oh, OK!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks