Date expression not giving desired result

[Neeraj_Malik]

I want to use a mechanism where entries for shift A and Shift B are allowed between specific time of the day

Shift A entries to be allowed between 10am to 9pm
Shift B entries to be allowed between 11pm to 9am
Date for Shift B should be of the previous day if entries are being made after 12am

To achieve this I’m using the following expression in date

ifs(
AND([Shift]="Shift A",TIMENOW()>Time("10:00:00"),TIMENOW()<=Time("21:00:00")),Today(),
AND([Shift]="Shift A",TIMENOW()>Time("21:00:00"),TIMENOW()<=Time("10:00:00")),"-NA-",
AND([Shift]="Shift B",TIMENOW()>Time("23:00:00")),TODAY(),
AND([Shift]="Shift B",TIMENOW()<=Time("09:00:00")),(Today()-1),
AND([Shift]="Shift B",TIMENOW()>Time("09:00:00"),TIMENOW()<=Time("23:00:00")),"-NA-"
)

along with validation on date as

OR(
[Date]=Today(),
[Date]=Today()-1,
[Date]<>"-NA-"
)

I feel the above should work perfectly but somehow the expression does’t seem to be working. If the part of the expression for Shift B alone is used, the Shift B dates are being calculated as expected. But part of the expression for Shift A, is not giving the desired result.

Request if someone can validation the above expression pls.

[Neeraj_Malik]

@LeventK, you usually respond very fast, can you help on this pls

[LeventK]

@Neeraj_Malik
You can use below expression as an Initial Value

IFS(
	"Shift A" = [Shift],
	IFS(
		AND(
			TIMENOW() >= TIME("10:00:00"),
			TIMENOW() <= TIME("21:00:00")
		),TODAY()
	),
	"Shift B" = [Shift],
	IFS(
		AND(
			TIMENOW() >= TIME("23:00:00"),
			TIMENOW() < TIME("0:00:00")
		),TODAY(),
		AND(
			TIMENOW() >= TIME("0:00:00"),
			TIMENOW() <= TIME("9:00:00")
		),TODAY()-1
	)
)

Additionally; you can’t specifiy a -NA- value in a data field as a validation rule. The validation rule shall be:

OR(
	[Date]=Today(),
	[Date]=Today()-1
)

[Neeraj_Malik]

LeventK:

IFS(
	"Shift A" = [Shift],
	IFS(
		AND(
			TIMENOW() >= TIME("10:00:00"),
			TIMENOW() <= TIME("21:00:00")
		),TODAY()
	),
	"Shift B" = [Shift],
	IFS(
		AND(
			TIMENOW() >= TIME("23:00:00"),
			TIMENOW() < TIME("0:00:00")
		),TODAY(),
		AND(
			TIMENOW() >= TIME("0:00:00"),
			TIMENOW() <= TIME("9:00:00")
		),TODAY()-1
	)
)

this expression is showing the same behavior as that of mine, only half of the expression is giving desire result…date is not being calculated if Shift B is selected

[Marc_Dillon]

This does not seem to be the optimal way to achieve your goal. It seems you are sort of combing validation for the Shift Time-period rules, as well as auto-generating the Date. But why? Why not just validate the Shift selection based on TIMENOW(), and then greatly simplify the expression for generating the Date? Why does your expression output “-NA-” under cases that are not valid. You can’t put a Text string into a Date type column. A column also doesn’t need validation if it is generated via expression.

Actually I really don’t see why you need any validation if you’re assuming entries are input at the appropriate times (since you’re using TIMENOW()), you can just auto-generate all of this data.

[Shift] =

IFS(
AND( TIMENOW()>“10:00:00” , TIMENOW()<=“21:00:00”),
“Shift A” ,
OR( TIMENOW()>“23:00:00” , TIMENOW()<=“09:00:00”),
“Shift B”
)

[Date] =

IF( TIMENOW() < “09:00:00” , TODAY()-1 , TODAY() )

[Neeraj_Malik]

@Marc_Dillon Indeed this is the better way of doing it. But not sure how this can fit into my overall requirement which is as follows

if [Txn Type] = “Paid” and [Shift] = “Shift A”, date should be Today() and allowed timing has to be 09:00 to 2100
if [Txn Type] = “Recd” and [Shift] = “Shift A”, date should be Today() and allowed timing has to be 09:00 to 2100
if [Txn Type] = “Recd” and [Shift] = “Shift B” then allowed timing has to be 21:00 to 0900 of next day and date has to be Today() for 21:00 to 00:00 and Today()-1 for 00:00 to 09:00
if [Txn Type] = “Paid” and [Shift] = “Shift B”, then allowed timing has to be 21:00 to 0900 of next day but the date shall be Today() for complete 24hrs

[Neeraj_Malik · Accepted solution]

@Marc_Dillon Your recommended expression on Shift worked with little customization to suit my need
Now I’m using Shift auto compute expression as

IFS(
AND( TIMENOW()>“00:00:00” , TIMENOW()<=“09:00:00”), “Shift B”,
AND( TIMENOW()<=“23:59:59” , TIMENOW()>“22:00:00”), “Shift B”,
AND( TIMENOW()>“09:00:00” , TIMENOW()<=“22:00:00”), “Shift A”
)

Along with [Shift] required only if [Txn Type]=“Recd”

And date auto compute expression as

IFS(
[Txn Type]="Paid",Today(),
[Txn Type]="Recd",IF( TIMENOW() < “09:00:00” , TODAY()-1 , TODAY() )
)

Thanks for guiding me to the far simpler expression

However, I would still be curious to understand why mine and @LeventK expression is not working in full and only half of the expression is giving result as both the expressions looks fine to me.

[LeventK]

@Neeraj_Malik
The last expression I have given in post#5 should work correctly for the Date part. In overall;

IFS(
	"Paid" = [Txn Type],TODAY(),
	"Recd" = [Txn Type],
	IFS(
		"Shift A" = [Shift],
		IFS(
			AND(
				TIMENOW() >= TIME("10:00:00"),
				TIMENOW() <= TIME("21:00:00")
			),TODAY()
		),
		"Shift B" = [Shift],
		IFS(
			AND(
				TIMENOW() >= TIME("23:00:00"),
				TIMENOW() < TIME("0:00:00")
			),TODAY(),
			AND(
				TIMENOW() >= TIME("0:00:00"),
				TIMENOW() <= TIME("9:00:00")
			),TODAY()-1
		)
	)
)

[Neeraj_Malik]

@LeventK in that cane I’m not sure what other error I’m making where date is not being calculated column goes hidden as soon as Shift B is selected.

[LeventK]

@Neeraj_Malik
With which code are you experiencing this?