• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested If statement based upon Time

Mark Meyers

New Member
Trying to write a nested if statement based upon time where:
If TIME > 22:00:00 but < 5:59:00, "Nights"
if TIME > 06:00:00 but < 13:59:00, "Days"
if TIME > 14:00:00 but < 22:00:00, "Evenings"

Thanks in advance for your assistance.
 
=IF(AND is your friend :)

See attachment...

Respectfully,
PaulF
 

Attachments

  • Shift_Times.xlsx
    16.5 KB · Views: 9
My 1st post has a mistake... Night part needed =IF(OR
 

Attachments

  • Shift_Times.xlsx
    16.5 KB · Views: 4
a slightly shorter version, using Paul's file:
D3:
=IF($C3<$G$3,$H$3,IF($C3<$G$4,$H$4,IF($C3<$G$5,$H$5,$H$3)))
or even shorter
=IF(OR($C3<$G$3,$C3>$G$5),$H$3,IF($C3<$G$4,$H$4,$H$5))
 
Sir,
I am also facing problem in formula having two things to be checked.

I have two inputs viz. Pay and City Grader. from which City Allowance to be displayed.

I have one column having 4 ranges of pay and before every pay range there are 4 grades of City, as per which city allowance is to given.

uploaded the file for your kind perusal.

1) if your pay is 1000 and city grd is B2 then CCA should be 120
2) if your Pay is 4500 and City Grd is A1 then CCA should be 200
Formula must check total 4 condition of Payrange and 16 conditions ( 4 condition per pay range ) of the City Grade



Pl help me in this regard. I got stuck what to be used in formula, is it if, multiple if, Hlookup, Vlookup, Index, Match functions, Bit confused and depressed with self, asking for help.
Thanks in advance,
Also I am unable to get into your forum to ask my query
In anticipation,

Jitendra Dixit, India
jjdixit@gmail.com
+91-9370902555
 

Attachments

  • CCA.xlsx
    9.1 KB · Views: 1
Hi Jitendra,

See this file.

Regards,
Thanks Somendra Ji........
how would we design a formula, where only conditions of pay range and citygrades are given ( and not the table).
Isn't it possible with if & and function.
But still the efforts taken by you are awesome. I am still not used to forum culture. Even some time unable to go where I want. I know how I got your reply.
thanks.... J J Dixit
 
@Jitendra J Dixit

I don't think using values directly in a IF condition is a good idea, for the reason,
1. Formula will be longer.
2. Changing the values in future will become cumbersome.

I would suggest you to use a table, may be you can save the table on a name manager. So it will not be visible on the sheet, but will remain available in memory.

Regards,
 
Back
Top