asonstad Posted February 13, 2005 Share Posted February 13, 2005 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. Link to comment Share on other sites More sharing options...
J Wenmeekers Posted February 14, 2005 Share Posted February 14, 2005 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. Link to comment Share on other sites More sharing options...
-Queue- Posted February 14, 2005 Share Posted February 14, 2005 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. Link to comment Share on other sites More sharing options...
J Wenmeekers Posted February 14, 2005 Share Posted February 14, 2005 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.... Link to comment Share on other sites More sharing options...
-Queue- Posted February 14, 2005 Share Posted February 14, 2005 Ah, I didn't notice the missing 'plus 1' in your If test. In that case, how about (Year(dateEnd + 1) - Year(dateStart + 1)) * 12 + Month(dateEnd + 1) - Month(dateStart + 1) - (Day(dateEnd + 1) Day(dateStart) and Day(dateEnd + 1) Link to comment Share on other sites More sharing options...
asonstad Posted February 17, 2005 Author Share Posted February 17, 2005 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. Link to comment Share on other sites More sharing options...
J Wenmeekers Posted February 17, 2005 Share Posted February 17, 2005 [ 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 ¿¿?? Link to comment Share on other sites More sharing options...
By Soliant Consulting,
By Soliant Consulting,
By Soliant Consulting,
Recommended Posts
Archived
This topic is now archived and is closed to further replies.