Date Calculation (1 Viewer)

[Gismo]

Hi all,

I need some assistance please

I need to calculate a series of dates and month and not sure on the way forward

Installation Date - 2022/02/01
Frequency - 24 Months
Todays Date - 2022/03/03
Consumed - 2 Months

Formula= Installed Date + Frequency - Today's Date - Consumed
Result to be in Months

I assume I need to use DateDiff in conjunction with DateAdd

 

[SHANEMAC51]

Formula= Installed Date + Frequency - Today's Date - Consumed
Result to be in Months

apostrophes and ampersands are not allowed in names as spaces and other separators

 

[Minty]

Something like

Firstpart = Dateadd([InstalledDate],"m",[Frequency) 
SecondPart = Datadd(Date(), "m", -[Consumed])

Result = FirstPart - SecondPart

 

[arnelgp]

DateDiff("m",Date,DateAdd("m",[Frequency], [Installation Date])) - (DateDiff("m", [Installation Date], Date)+1)

 

[SHANEMAC51]

I need to calculate a series of dates and month and not sure on the way forward

it is not clear what you need - to count and lay out several dates

possible dates=2022/02/01 will be dates with a periodicity of 24 or 2 months

2022/02/01 2024/02/01 2026/02/01 ....
or
2022/02/01 2022/04/01 2022/06/01 ....

 

[Gismo]

DateDiff("m",Date,DateAdd("m",[Frequency], [Installation Date])) - (DateDiff("m", [Installation Date], Date)+1)

Why does Date add "m" give me a weird date when adding a date and 24 months?

Expr1: DateAdd("m",[Overview Tbl]![Installation Date 1],[Overview Tbl]![TBO 2])

The date field is formatted

 

[SHANEMAC51]

Why does Date add "m" give me a weird date when adding a date and 24 months?

expr1: DateAdd("M", 24, [Overview Tbl]![Installation Date 1])

 

[arnelgp]

what is your installation date 1?
see query1.

 

[Gismo]

expr1: DateAdd("M", 24, [Overview Tbl]![Installation Date 1])

Am I missing something here?

I replaced the - with a , as this is the DateDiff

DateDiff (DateAdd("m",[Overview Tbl]![TBO 2],[Overview Tbl]![Installation Date 1]) , (DateAdd("m",[Overview Tbl]![Consumed 1],Date()))

 

[Minty]

Don't format dates in tables, do that on the forms controls.
Formatting is for display purposes, not for storing data.

Did you try the breakdown - it can be amalgamated into one single line - but while debugging breaking things into stages, especially when you are unfamiliar with the syntax can be very beneficial to getting things right.

Edit : my syntax is out apologies written in haste

Firstpart = Dateadd("m",[Frequency],[InstalledDate])
SecondPart = Dateadd("m", -[Consumed], Date() )

Result = FirstPart - SecondPart

 

[Gismo]

what is your installation date 1?
see query1.

Installation date is 2022/02/01

There is also a Consumed which is in months

Installation Date - 2022/02/01
Frequency - 24 Months
Todays Date - 2022/03/03
Consumed - 2 Months

Formula= Installed Date + Frequency - Today's Date - Consumed
Result to be in Months

 

[Gasman]

Well according to your formula?

tt=datediff("m",dateadd("m",24,#2022/02/01#),dateadd("m",-2,date))

 

[arnelgp]

based on your sample (maybe i am wrong), Consumed can be calculated from [Installation Date]:

(DateDiff("m",[Installation Date 1],Date())-(Day(Date())>=Day([Installation Date 1])))

which means:

month difference between [installation date] and [today's date] +
add 1 if [today's day] >= [installation date day].