Date formula

[Jwwwson]

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, 

[deviseetharaman · Accepted 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

[Jwwwson]

@deviseetharaman Thanks! It works!