Facilities management tracker - tracking services done in each month

Charlie H

Hi all,

i am working in the FM sector and i am managing a clients servicing for many of their retail stores.

i have built a tracker with numerous formulas in. Each service/line has a ‘Date of last inspection” column and a “Date of next inspection” column drived by the frequency of the visit in another column with a simple SUM formula of the date of last inspection + frequency. These dates will be changed manually once it has been superseded therefore populating a new next service date and new date of last service.

i am looking to build a dashboard showing all of the services due in each month , i understand to do a helper column with COUNTIF/MONTH formula 1,2,3 etc.. However, once I have the amount of services due in month 6 (June 2024) at let’s say 300 due, I want a dashboard to show that 300 were due in month ‘6’ and only 100 have been completed so far, therefore leaving 200 left to do. This is an overview for the client.

i am struggling to work out how I can show how many services have been done out of the exampled 300 due in month 6 , due to the dates getting changed once the service has been done in that month. Is there anyway around this at all , as the helper column formula picks up month ‘6’ but when the dates get changed it will not show as month 6 potentially in the next service date column.

Hope this makes sense all, it’s a tricky one to type out, any help/advise will be great

KPH

You can still use a COUNTIF formula for your metrics. You would do this on the data in the archive sheet rather than the tracker sheet.

For flexibility, I would set up a little table in a third sheet and use cross sheet references to count the values in the two columns in the archive sheet.

If you aren't familiar with cross sheet references take a look at this:

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

More details

I'm not sure how familiar you are with formulas and automations so here are some additional details.

If you have a tracking sheet like this:

Set up an automation like this:

And change the last service dates in your sheet each time a vehicle is serviced.

Then, your archive sheet might start to look like this:

This keeps a record of the changes and gives you have all the data in one place.

You can create a report from this. Or you can make your own metrics table which will give you more flexibility. I would also include the year in the metrics table.

Create a sheet like this.

Then in the Number Due column you can add a COUNTIFS formula (we are using COUNTIFS not COUNTIF so that we can include the year as well as the month).

=COUNTIFS({Archive Service Due}, IFERROR(MONTH(@cell), 0) = [Month Number]@row, {Archive Service Due}, IFERROR(YEAR(@cell), 0) = Year@row)

The part in {} indicates a cross sheet reference. This needs to be created in the sheet, you can't just copy and paste. To create it, start entering the formula and a pop up box will give you the option to reference another sheet. Click on that. Select your archive sheet from the tree. Select the column. Give it a name.

The formula is counting the number of rows in the archive sheet where the month of the date in the next service column equals the month in the metrics table and the year of the date in the next service column equals the year in the metrics table.

You can do the same for the last service column to find the number that have been serviced:

=COUNTIFS({Archive Last Service Date}, IFERROR(MONTH(@cell), 0) = [Month Number]@row, {Archive Last Service Date}, IFERROR(YEAR(@cell), 0) = Year@row)

And add a little percentage at the end if you want:

=IFERROR([Number Serviced]@row / [Number Due]@row, "")

You could add another column to make it more user friendly (and hide Year and Month).

=MID("JanFebMarAprMayJunJulAugSepOctNovDec", ([Month Number]@row * 3) - 2, 3) + " " + Year@row

All three formula can be made into column formula.

You can now create a row report from this sheet to add it to your dashboard. Or create a chart widget.

KPH

Hi @Charlie H

I think a second sheet that is automatically populated with rows as the dates change could be a solution for you.

As I understand it, you have 1 sheet with latest service date and formula to determine the next due date. When a service is completed the latest service date is manually changed and the next service is calculated. This all sounds good.

But now you need to report on what the latest service date was before it was changed. And that information has now vanished.

My suggestion is to...

1. Create a blank sheet as an "Archive".
2. Add an automation to the first sheet that is triggered when latest service date is added or changed.
3. Set this automation to copy the row to the archive.

So now, when a service date is added, the first sheet updates the next due date and the row is copied to the archive. When the service is carried out, the date is updated, the next due date changes, and the row is again copied to the archive.

You can then report on all the latest service dates.

I hope this makes sense.

Charlie H

Thanks for the response here.

all sounds good and is a great idea. How would I then in my metrics sheet reference back to the 300 due in month 6 (on my original sheet) to then reference to the archive sheet and to take the original date it was before it was changed.

How could I link the 2 together so then in my dashboard it will show as 1 completed and 299 left to be completed in that month?

I hope I am making sense here, thanks for the help!

KPH

You can still use a COUNTIF formula for your metrics. You would do this on the data in the archive sheet rather than the tracker sheet.

For flexibility, I would set up a little table in a third sheet and use cross sheet references to count the values in the two columns in the archive sheet.

If you aren't familiar with cross sheet references take a look at this:

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

More details

I'm not sure how familiar you are with formulas and automations so here are some additional details.

If you have a tracking sheet like this:

Set up an automation like this:

And change the last service dates in your sheet each time a vehicle is serviced.

Then, your archive sheet might start to look like this:

This keeps a record of the changes and gives you have all the data in one place.

You can create a report from this. Or you can make your own metrics table which will give you more flexibility. I would also include the year in the metrics table.

Create a sheet like this.

Then in the Number Due column you can add a COUNTIFS formula (we are using COUNTIFS not COUNTIF so that we can include the year as well as the month).

=COUNTIFS({Archive Service Due}, IFERROR(MONTH(@cell), 0) = [Month Number]@row, {Archive Service Due}, IFERROR(YEAR(@cell), 0) = Year@row)

The part in {} indicates a cross sheet reference. This needs to be created in the sheet, you can't just copy and paste. To create it, start entering the formula and a pop up box will give you the option to reference another sheet. Click on that. Select your archive sheet from the tree. Select the column. Give it a name.

The formula is counting the number of rows in the archive sheet where the month of the date in the next service column equals the month in the metrics table and the year of the date in the next service column equals the year in the metrics table.

You can do the same for the last service column to find the number that have been serviced:

=COUNTIFS({Archive Last Service Date}, IFERROR(MONTH(@cell), 0) = [Month Number]@row, {Archive Last Service Date}, IFERROR(YEAR(@cell), 0) = Year@row)

And add a little percentage at the end if you want:

=IFERROR([Number Serviced]@row / [Number Due]@row, "")

You could add another column to make it more user friendly (and hide Year and Month).

=MID("JanFebMarAprMayJunJulAugSepOctNovDec", ([Month Number]@row * 3) - 2, 3) + " " + Year@row

All three formula can be made into column formula.

You can now create a row report from this sheet to add it to your dashboard. Or create a chart widget.

Charlie H

Absolute legend mate, really makes sense.

thank you so much, massive help and very kind to give up your own time to explain.

thank you, if it doesn’t work for me I will come back and let you know.

Charlie H

Just one more question please.

The 300 what were due in that month (from my original sheet), I understand when we manually change a date and it moves over into the archive - how will it then still pick up the original 300 what were due in that month due to the date being changed and next service date is different in my original sheet?

moving into the archive will show how much has been done in that month , but how will my denominator (original 300 due in June from original sheet) link up against what has been moved into the archive.

will the formulas given above automatically do this?

Thanks

Charlie H

Ignore my last comment.

I have added all in and all has worked , very impressive and unbelievable advice

thank you very much

KPH

Happy to hear that worked for you. The denominator works because the archive is also archiving the due date when the last service date is changed. Every date is recorded in that archive. The only issue you may have is with historical services. In my example, you will see I completed two services in January but did not have any due. That is because the due dates for services that were last carried out prior to the sheet being introduced, are not included. If this is an issue for you, you could add them manually to the archive. Just add a list of due dates with no last service date, as a starting point.

Charlie H

I noticed this, I am going to copy and paste the date of last inspections into a blank spreadsheet-

in my tracker I will then take out the dates and save - then just copy them all back in and save it again. It will pull through then I believe?

Off topic- I have another question a different one to solve if you could help please? I have raised it as a new question if it is in my profile?

Thanks mate

KPH

I'm not sure I follow your plan but I don't recommend taking things out of the tracker and then putting them back in again as putting them back in will cause each row to copy again leading to duplicate entries in your archive. Unless you plan to delete all rows from the archive as well and are starting over. You could just add an initial next service for each item directly to the archive sheet like this:

The tracker does not change.

Metrics would automatically update to:

I am heading out now, but will check on the other question you have when I am back at my desk.