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.
Solved! Go to 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, you usually respond very fast, can you help on this pls
@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
)
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
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() )
@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
@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.
@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
)
)
)
@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.
@Neeraj_Malik
With which code are you experiencing this?
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |