I have an object table holding data on “cctv towers” connected to a “client” object table.
In the “cctv tower” object table I have a field for “start hire date” and “finish hire date”, also a status for “in Service” and “in stock”
I’m trying to construct a page for invoicing purposes. It lists each client with their connected cctv towers. But I only want it to display towers that are still currently “in service” but also those towers that have a “finish hire date” in this month (and I might need a different page for previous month in case we miss the invoicing in the current month).
Then, I need it to calculate the amount of weeks that is billable for that current month.
I have created a total hire duration by equation " currentTime()-{Lastest In Service date} "
But I’ll need 4 equations that I dont know how to do;
Calc1
For a tower that is billable for the whole month (based on a start date before current month and a blank finish date)
Calc2
For a tower that has a start date in the current month and a blank finish date
Calc3
For a tower that has a start date before current month and a finish date in the current month
Calc4
For a tower that has a start date and a finish date in the current month
Any ideas??