Forum Discussion
Multiple Windows of Time
Hi All,
I recently discovered the SUMX function and I think its great! however, what I need to do is calculate the number of operating days for a specific piece of equipment. I've got tables below to demonstrate what I believe the data should look like. Ultimately, my end result will be to come up with a (filterable) number of days that each piece of equipment has been operational based on its install date and the open days of the location its installed in.
In my case, the first assumption is that I'm measuring only days since January 31st 2021. For all intents and purposes where locations have been shut down a second time, there will be no equipment installed in this duration of time.
Equipment Table
| Location Number | Equipment Serial | Install Date |
| 1 | DeviceSN1 | November 1 2020 |
| 1 | DeviceSN2 | November 2 2020 |
| 2 | DeviceSN3 | February 5 2021 |
| 3 | DeviceSN4 | March 20 2021 |
Location Closure Table
| Location | Close Date | Re-Open Date |
| 1 | December 27 2020 | March 8 2021 |
| 1 | April 7 2021 | May 8 2021 |
| 2 | December 27 2020 | March 8 2021 |
| 2 | April 7 2021 | May 8 2021 |
| 3 | April 10 2020 | May 1 2020 |
5 Replies
- amitchandak
Super User
Anonymous , the second table does not have any Equipment details. Then which record belongs to which?
Can you share sample output in table format?
The situation is that my workplace has purchased some equipment, this equipment needs to be measured against its daily performance (Units produced per day). However, some of the locations in which these units are installed have experienced closures and the organization has asked for the number of operating days to be adjusted to reflect the closures. We have a matrix of scenarios. Whatever the solution, I can apply it to only the current fiscal year (started January 31st) or I can apply it to since the equipment was installed.
Equipment Install Scenario
• Before January 31st
• After January 31st during closure
• After January 31st and after store has re-opened
Location Closure Scenario
• Location has had no closures this year
• Location has had 1 closure this year
• Location has had 2 closures this year
How I would calculate the math “manually” is:
• If the equipment was installed before the fiscal year, I would calculate the total operational days and then subtract all the closed days (from either 1 closure window or 2 closure windows)
• If the equipment was installed after the fiscal year but before the store re-opened, I would calculate the days since open (and then subtract any close days that may take place after the location has re-opened)
• If the equipment was installed after the location re-opened I would calculate days after the installation date (and then subtract any close days that may take place after the equipment is installed)
v-kelly-msftCommunity Support
Hi Anonymous ,
For location1, Equipment Serial DeviceSN1,the total operation days is Date(2020,12,27)-date(2020,11,1) add Today()-Date(2021,3,8)，right?
If possible,can you pls provide a calculation formula or output in numerical calculation format?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
