SirSquiddly
New Member
- Joined
- Jun 26, 2018
- Messages
- 40
Hi
I have a large spread sheet that needs to be analysed at the end of each month. I need some help trying to make this much more efficient.
Currently on hire dates are in a column (R) and off hire dates in the next (S).
R S
[TABLE="width: 251"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]20/05/2019[/TD]
[TD]06/06/2019[/TD]
[/TR]
[TR]
[TD]20/05/2019[/TD]
[TD]06/06/2019[/TD]
[/TR]
[TR]
[TD]20/05/2019[/TD]
[TD]06/06/2019[/TD]
[/TR]
[TR]
[TD]22/05/2019[/TD]
[TD]08/06/2019[/TD]
[/TR]
[TR]
[TD]02/06/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]24/05/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28/07/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28/07/2019[/TD]
[TD]14/07/2019[/TD]
[/TR]
[TR]
[TD]28/07/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26/06/2019[/TD]
[TD]5/08/2019[/TD]
[/TR]
</tbody>[/TABLE]
So I have to do a monthly report and need to filter out everything from other months and have a total day count as well. Lets use July for example. There are some hire tools that leave in June gone and return in august (bottom example above) or are not yet returned by months end (blank return column) but both would require 31 days for July. Also, there are some tools start date in June and return in July but I would only need to calculate the days for July.
Can anyone suggest an easier way to do this than manually sorting through the dates and adding 01/07/19 to some of the start dates and 31/07/2019 to the off hire dates. There are hundreds of rows each month.
Any help would be hugely appreciated. Thanks
I have a large spread sheet that needs to be analysed at the end of each month. I need some help trying to make this much more efficient.
Currently on hire dates are in a column (R) and off hire dates in the next (S).
R S
[TABLE="width: 251"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]20/05/2019[/TD]
[TD]06/06/2019[/TD]
[/TR]
[TR]
[TD]20/05/2019[/TD]
[TD]06/06/2019[/TD]
[/TR]
[TR]
[TD]20/05/2019[/TD]
[TD]06/06/2019[/TD]
[/TR]
[TR]
[TD]22/05/2019[/TD]
[TD]08/06/2019[/TD]
[/TR]
[TR]
[TD]02/06/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]24/05/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28/07/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]28/07/2019[/TD]
[TD]14/07/2019[/TD]
[/TR]
[TR]
[TD]28/07/2019[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26/06/2019[/TD]
[TD]5/08/2019[/TD]
[/TR]
</tbody>[/TABLE]
So I have to do a monthly report and need to filter out everything from other months and have a total day count as well. Lets use July for example. There are some hire tools that leave in June gone and return in august (bottom example above) or are not yet returned by months end (blank return column) but both would require 31 days for July. Also, there are some tools start date in June and return in July but I would only need to calculate the days for July.
Can anyone suggest an easier way to do this than manually sorting through the dates and adding 01/07/19 to some of the start dates and 31/07/2019 to the off hire dates. There are hundreds of rows each month.
Any help would be hugely appreciated. Thanks