Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community ;
please help me for below query.
BCD | DEPDATE & TIME | |||
23-07-2021 | 2021-08-12 - 1705 / 2021-08-12 - 2030 | |||
28-03-2022 | 2022-04-06 - 1340 | |||
11-01-2023 | 2022-11-21 - 0645 | |||
06-01-2023 | 2022-07-12 - 0000 / 2022-7-12 - 0000 |
BCD | DEPDATE & TIME | day(bcd-depate & time) | |||||
21-12-2021 | 2021-12-26 - 1705 / 2021-12-30 - 2030 | 9 | |||||
28-03-2022 | 2022-04-06 - 1340 | 9 | |||||
11-01-2023 | 2022-11-21 - 0645 | -51 |
floor(date#(right([DEPTDATE&TIME],17),'YYYY-MM-DD - HHmm'))
-
floor(date#([BCD],'DD-MM-YYYY'))
I apply the logic but day(bcd-depate & time) column is blank. not getting any values in day(bcd-depate & time) column
its working now but how to set 0 if i found negative number.
if(
floor(date#(right([DEPTDATE&TIME],17),'YYYY-MM-DD - HHmm')) - floor(date#([BCD],'DD-MM-YYYY')) < 0,0,
floor(date#(right([DEPTDATE&TIME],17),'YYYY-MM-DD - HHmm')) -
floor(date#([BCD],'DD-MM-YYYY'))
)
Thanks A lot .its working fine but i have one doubt
some time my depdate & time is : like (13:00) or some time is like(1300) i..so i write 17 or 18 in the code
BCD | DEPDATE & TIME | days | |
13-02-2023 | 11-02-2023 - 0630 | -2 but i get (0) (correct according to query) | |
03-03-2023 | 2023-03-01 - 13:00 | but here date is not 0 beacuse of this colon 13:00 (here total length is 18 not 17) |
how i solve this type format beacuse some times i getting colon and sometimes not .
if(
floor(date#(right(purgechar[(DEPTDATE&TIME],':'),17),'YYYY-MM-DD - HHmm')) - floor(date#([BCD],'DD-MM-YYYY')) < 0,0,
floor(date#(right(purgechar([DEPTDATE&TIME],':'),17),'YYYY-MM-DD - HHmm')) -
floor(date#([BCD],'DD-MM-YYYY'))
)
Thanks marijn 😊