Maintenance Downtime Tracker

[mlee1]

Hello,

First off I would like to say I am very new to macros and VBA in excel. I am trying to create a database that will log and track when our equipment is taken out of service and back in service. I would like for when my techs take a forklift out of service they click on the lift number then themselves and out of service. this in turn will record that lift number who worked on it and a time stamp of when it was taken out of service on sheet2 of the work book. Then when they click in service it adds that time stamp to that same lift. I just have no idea how to create that.

 

[RasGhul]

Re: Maintenance Downtime Tracker help

Argh good,

I've fixed the date entry so that the service date maintains the submitted date. In the Userform you now Choose Forklift & Tech as before and this will add new Out of Service timestamps.

When the service is completed choose the forklift and the Tech & unique service code (made from service date & Forkilft & Tech) will appear in the userform. When you press service completed it adds "operational" & time stamp as below.

Book1
	A	B	C	D	E	F	G	H
1	Date	Forklift	Tech	Status	Time Stamp	Unique Service Code	Status	Time Stamp
2	02-03-19	Lift2	Tech3	Out of Service	02-03-19 21:24	43526Lift2Tech3
3	02-03-19	Lift4	Tech5	Out of Service	02-03-19 21:26	43526Lift4Tech5	Operational	20-03-19 12:43
4	19-03-19	Lift1	Tech8	Out of Service	19-03-19 21:40	43543Lift1Tech8
5	20-03-19	Lift2	Tech1	Out of Service	20-03-19 21:40	43544Lift2Tech1	Operational	20-03-19 12:33
6	20-03-19	Lift6	Tech7	Out of Service	20-03-19 12:37	43544Lift6Tech7	Operational	20-03-19 12:41
7	20-03-19	Lift10	Tech10	Out of Service	20-03-19 12:43	43544Lift10Tech10
Database

new dropbox link

https://www.dropbox.com/s/nift4b6tzy64y4s/Forklift Service Log.xlsm?dl=0

 

[mlee1]

Re: Maintenance Downtime Tracker help

Ras,

Thank you for all your help! This is looking great. I have found a couple issues. The sheet will not allow me to select a technician when putting back to operational and if I manually enter the new techs name into the tech spot and into the service code it will not log. I am not sure if this is a difficult issue to solve if it is then i am not too worried about that. The last thing is that i moved the log service button back to the service tab and now whenever i log data it changes the screen from service to database how would i go making sure that the screen stays on the service tab while logging data?

 

[mlee1]

Re: Maintenance Downtime Tracker help

Ras,

I have been playing with it more and found that after a lift lets say lift1 is put back to operational at 2pm by tech 1 and then taken out of service by tech2 at 4pm once i go to put operational it auto fills back to tech 1 from the original entry and will not allow me to log data.

 

[RasGhul]

Re: Maintenance Downtime Tracker help

I've change the unique code to be based on the time stamp rather than just the date to account for multiple events during the day.

In the below scenarios it looks like it is now adding operational to the last/or most recent out of service event;

Book1
	A	B	C	D	E	F	G	H
1	Date	Forklift	Tech	Status	Time Stamp	Unique Service Code	Status	Time Stamp
2	21-03-19	Lift1	Tech1	Out of Service	21-03-19 9:26	43545.3935511574Lift1Tech1
3	21-03-19	Lift1	Tech1	Out of Service	21-03-19 9:26	43545.3936409722Lift1Tech1
4	21-03-19	Lift1	Tech1	Out of Service	21-03-19 9:26	43545.3937168982Lift1Tech1	Operational	21-03-19 9:28
5	21-03-19	Lift2	Tech2	Out of Service	21-03-19 9:27	43545.3946855324Lift2Tech2
6	21-03-19	Lift2	Tech3	Out of Service	21-03-19 9:28	43545.3947780093Lift2Tech3
7	21-03-19	Lift2	Tech4	Out of Service	21-03-19 9:28	43545.394855787Lift2Tech4	Operational	21-03-19 9:28
Database

I've put the button back to the Service sheet as requested.

https://www.dropbox.com/s/nift4b6tzy64y4s/Forklift Service Log.xlsm?dl=0

 

[mlee1]

Re: Maintenance Downtime Tracker help

Ras,

Again thank you very much for your assistance in this project. I am giving it to my guys today to use.

 

[RasGhul]

Re: Maintenance Downtime Tracker help

Youre welcome. If anything else pops up let me know

 

[mlee1]

Re: Maintenance Downtime Tracker help

Hi Ras,

So since this has been working I am looking to take it a step further for the reporting aspect. Right now i have been copy/ pasting the data from this worksheet to another and using a custom time format to give me a total downtime in a 24 hour period (7am -7am). This is a two part question, is it possible to have the data export to another worksheet automatically? Two: is it possible to build a report that i say i want the downtime between 7am 4/8 and 7am 4/9 and have that info display without manually filtering?

 

[mlee1]

Re: Maintenance Downtime Tracker help

Attached is the sheet that i am using. I am using the second tab called downtime data, which is where i copy/paste too.

https://docs.google.com/spreadsheets/d/1VFYPEQuWHsaAMLKBaae_s2niNy7swWUqst3lh8o7ZXY/edit?usp=sharing

 

[RasGhul]

Re: Maintenance Downtime Tracker help

Hi mlee,

Question1 yes I can send data to multiple sheets automatically, but there is also no need to as you can extract data with formulas.

Queston2 Check my mock up copy of your sheet and see if this suits e.g between 7am 8th of March - 7am 9th of March there are 12 rows of data returned.

https://www.dropbox.com/s/qbipo74yffm77x9/downtime tracking_jp.xlsx?dl=0

 

[mlee1]

Re: Maintenance Downtime Tracker help

Hi Ras,

This looks amazing. Thank you. One thing i am seeing though is that not all of the records are pulling. I copy/ pasted the rest of yesterdays data to the sheet. You can see that it does not pull all of the records for 4/9 it leaves out the bottom 7 records. Additionally, the downtime amount on the left column is not matching the downtime numbers of the pulled records on the right.

https://docs.google.com/spreadsheets/d/1orez4hj5iW1Pjeh8pII083BaHKvCWpTzr46vuoHXK74/edit?usp=sharing