IF function on Time

[Shinianne]

Hi can I please ask for help is it possible to use IF to Time?


If its between 08:00 - 15:59 CST time it will be AMER
If its between 16:00 - 23:59 CST Time APAC
If its between 00:00 - 07:59 CST Time EMEA


I tried =IF(AB2<TIME(15,59,0),"AMER",IF(AB2<TIME(23,59,0),"APAC",IF(AB2<TIME(7,59,0),"EMEA")))


and its not working

 

[AlanY]

if your time in A1, try

=IF(A1>=TIME(16,0,0),"AMER",IF(A1>=TIME(8,0,0),"APAC","EMEA"))

 

[Shinianne]

if your time in A1, try

=IF(A1>=TIME(16,0,0),"AMER",IF(A1>=TIME(8,0,0),"APAC","EMEA"))

Hi I tried it but its not falling on the criteria,

[TABLE="width: 153"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Time[/TD]
[TD]REGION[/TD]
[/TR]
[TR]
[TD]04:27:01[/TD]
[TD]AMER[/TD]
[/TR]
[TR]
[TD]04:23:46[/TD]
[TD]AMER[/TD]
[/TR]
[TR]
[TD]03:36:45[/TD]
[TD]AMER[/TD]
[/TR]
[TR]
[TD]03:17:53[/TD]
[TD]AMER[/TD]
[/TR]
</tbody>[/TABLE]

Its showing AMER on 4:00:00 CST which should show as EMEA

 

[AlanY]

works for me

Book1
	A	B
1	Time	REGION
2	04:27:01	EMEA
3	04:23:46	EMEA
4	09:36:45	APAC
5	23:17:53	AMER
Sheet1

Cell Formulas
Range	Formula
B2	=IF(A2>=TIME(16,0,0),"AMER",IF(A2>=TIME(8,0,0),"APAC","EMEA"))

 

[Shinianne]

works for me

	A	B
Time	REGION

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]04:27:01[/TD]
[TD="bgcolor: #E2EFDA"]EMEA[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]04:23:46[/TD]
[TD="bgcolor: #E2EFDA"]EMEA[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]09:36:45[/TD]
[TD="bgcolor: #E2EFDA"]APAC[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]23:17:53[/TD]
[TD="bgcolor: #E2EFDA"]AMER[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(A2>=TIME(16,0,0),"AMER",IF(A2>=TIME(8,0,0),"APAC","EMEA"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The formula for my time is =TEXT(L2,"HH:MM:SS") if i use the formula it wouldnt work for me ..

 

[FormR]

The formula for my time is =TEXT(L2,"HH:MM:SS") if i use the formula it wouldnt work for me ..

Hi, what exactly do you have in L2? If it's a date and time and the text() function is an attempt to extract just the time then use this formula instead and apply a number format of HH:MM:SS to display it the way want.

=MOD(L2,1)