Salesforce and other SMB Solutions are coming soon. ×

# Calculating periods

## Recommended Posts

I have a file with field "Category" (categories are Recieved, Started, Pause, Restarted and Ended) and a date field "Date". I want to calculate the number of months of activity (between Started-Paused and Restarted-Ended), and not the periods with inactivity (Pause-Restarted).

Anyone have a solution? Ive tried many things, but not solved it.

##### Share on other sites

One rough, basic approach could be:

Elaps month - calc - number:

(Year(dateEnd + 1) - Year(dateStart + 1)) * 12 + (Month(dateEnd + 1) - Month(dateStart + 1)) -

If( Day(dateEnd + 1) = Day(dateStart); 0; If( Day(dateEnd + 1)

do the same for the 'in between time':

Elaps pause - calc - number:

(Year(dateRestart + 1) - Year(datePause + 1)) * 12 + (Month(dateRestart + 1) - Month(datePause + 1)) -

If( Day(dateRestart + 1) = Day(datePause); 0; If( Day(dateRestart + 1)

Make the diff between the two.

You can combine the two calcs with some case statements when there is or isn't 'pause time' etc.

##### Share on other sites

Simplifying the first calc,

(Year(dateEnd + 1) - Year(dateStart + 1)) * 12 + Month(dateEnd + 1) - Month(dateStart + 1) - (Day(dateEnd + 1)

Repeat for the between calc.

##### Share on other sites

For most cases you're right...

But if the start date is the first day of a month and the end date is the last day of a month (f.i 1/1/2005 - 31/3/2005), your solution gives 2, instead of 3....

##### Share on other sites

Ah, I didn't notice the missing 'plus 1' in your If test.

(Year(dateEnd + 1) - Year(dateStart + 1)) * 12 + Month(dateEnd + 1) - Month(dateStart + 1) - (Day(dateEnd + 1) Day(dateStart) and Day(dateEnd + 1)

##### Share on other sites

Rough perhaps, but good enogh for me :-) It worked very well. My only problem now is how to determine wich records should be used in the formula. There is also another record with information not relevant for this calculation.

##### Share on other sites

[ QUOTE ]

...only problem now is how to determine wich records should be used in the formula. There is also another record with information not relevant for this calculation.

[/ QUOTE ]

The calc is on field level and should work for all the records Â¿Â¿??

#### Archived

This topic is now archived and is closed to further replies.

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.5k
×
×
• Create New...