Hi,
I would like to seek your kind advice on the date formula. Please refer to below for the data:
Shop | Closing date | Expected revenue (M) - 1 May 2020 - 15 May 2020 | Expected revenue (M) - 16 May 2020 - 31 May 2020 |
1 | 02-02-20 | 80 | 80 |
2 | 06-06-20 | 160 | 20 |
3 | 05-05-20 | 32 | 35 |
4 | 18-05-20 | 48 | 80 |
What I would like to do is to decide if I shall keep the expected revenue of the corresponding shop or not. Here is some scenarios, the first one is the shop closed before May; second scenario is the shop close after May 2020; third one is the shop closed during the first half of May; and the last one is it closed during the second half of May. And I would like to produce the results as shown below, which is, for shop that closed during the first half of May, all the expected revenue for whole May is deemed to be 0; while if it closed during the second half of May, only the expected revenue of second half of May is deemed to be zero.
Shop | Closing date | Expected revenue (M) - 1 May 2020 - 15 May 2020 | Expected revenue (M) - 16 May 2020 - 31 May 2020 |
1 | 02-02-20 | 0 | 0 |
2 | 06-06-20 | 160 | 20 |
3 | 05-05-20 | 0 | 0 |
4 | 18-05-20 | 48 | 0 |
Is there any simple workflow that can achive this goal? Many thanks in advance.
Cheers,
Solved! Go to Solution.
Hi @Jwwwson
Please try using the below formula
if DateTimeMonth([Closing date]) < 5 then 0 elseif DateTimeMonth([Closing date]) = 5 and DateTimeDay([Closing date]) < 15 then 0 else [Expected revenue (M) - 1 May 2020 - 15 May 2020] endif
if DateTimeMonth([Closing date]) <= 5 then 0 else [Expected revenue (M) - 16 May 2020 - 31 May 2020] endif
@deviseetharaman Thanks! It works!