Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calculate months elapsed


Grant Symon
 Share

Recommended Posts

Hi,

 

I'm struggling with the logic on this ... I'm sure it's a breeze for you 'pros'.

 

I want to calculate the number of months elapsed between a date field and 'today'. That bit I can manage without any difficulty.

 

The problem is that I only want it to return full calendar months. IOW, if the date Today is the 21/9/2006 and the dateField that it's calculating on has 22/8/2006, then I want it to return 0. However, if the dateField has 20/8/2006, then I want it to return 1.

 

This is what I have so far for dealing with years :

 

____________________________________________________________

Case ( Year ( Get ( CurrentDate ) ) > Year ( Settings_date last run )

; Month ( Get ( CurrentDate ) ) - Month ( Settings_date last run ) + (Year ( Get ( CurrentDate ) ) - Year ( Settings_date last run )) *12

 

; Year ( Get ( CurrentDate ) ) = Year ( Settings_date last run )

; Month ( Get ( CurrentDate ) ) - Month ( Settings_date last run )

 

;0)

____________________________________________________________

 

The above obviously fails if ... Day ( Settings_date last run ) ... returns > Today.

 

Any help/info/suggestions ... much appreciated!!

 

Grant

Link to comment
Share on other sites

Try this calcualtion:

 

( Year ( dateTo ) * 100 + Month ( dateTo ) ) - 
( Year ( dateFrom ) * 100 + Month ( dateFrom ) )
- Case( Day ( dateFrom ) > Day ( dateTo ); 1)

 

Since you want to compare to today, make sure the result is unstored and use:

 

( Year ( Get ( CurrentDate ) ) * 100 + Month ( Get ( CurrentDate ) ) ) - 
( Year ( dateFrom ) * 100 + Month ( dateFrom ) )
- Case( Day ( dateFrom ) > Day ( Get ( CurrentDate ) ); 1)

 

See how it works? A bit of lateral thinking there :cool:

Link to comment
Share on other sites

Hi David,

 

many thanks for the help.

 

It works!

 

I had to change the 100 to 12 before it worked ... but that was no hardship. :)

 

However, I have absolutely *no idea* why it works. Would you mind explaining the logic to me? :(

 

Grant

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use