An official website of the United States government
Here's how you know
A .mil website belongs to an official U.S. Department of Defense organization in the United States.
A lock (lock ) or https:// means you’ve safely connected to the .mil website. Share sensitive information only on official, secure websites.

Innovation saves 37 work-hours using spreadsheet

  • Published
  • By Senior Airman Taryn Butler
  • 386th Air Expeditionary Wing Public Affairs

In a deployed environment, unit travel representatives (UTR) have to find an extra 40 hours to complete the duties required of them within a five-day span. On top of the typical 12-hour shifts, six days a week they spend on their primary duties, UTRs are responsible for tracking unit members’ travel back home.

U.S. Air Force Tech. Sgt. Caleb Jones, 386th Expeditionary Force Support Squadron manpower analyst, created a macro, a recorded series of instructions, with a spreadsheet that cuts the 40 hours of work into three hours or less by automating about 90% of a UTR’s duty when it comes to a data scrub, a process used to determine redeployment dates.

“It was not fun creating the macro, but I really saw the value of it,” said Tech. Sgt. Caleb Jones, 386th Expeditionary Force Support Squadron manpower analyst. “When the first scrub happened, I did seven units because people [kept asking me for help.] After the seventh person, I thought, ‘these guys really need help.’ There were still about 20 units out there I didn't touch that probably were having the same struggle, so we came up with something that's easy and can help everybody.”

Before the macro, UTRs manually pulled and adjusted 25 data points on a deployed individual, which took about five to 10 minutes for one person. However, that time adds up quickly when a UTR, like Senior Airman Katherine Meyer, 386th Expeditionary Maintenance Group, has more than 250 deployed personnel to process in addition to her primary duties.

“The old way of doing the scrub was very demanding,” said Meyer. “You had to go line by line and manually input the information that was either blank or incorrect. You have to do that for all the members and it becomes very draining. You’re more likely to miss something.”

The macro automates and populates most of the information the UTR would have otherwise had to enter manually. It does this using approximately 700 lines of code.

“It’s a lot easier to have 90% of the information already completed and focus on the information that is incorrect,” Meyer added. “There are things that pop up every now and then that take precedence over the scrub, so having a good balance with job proficiency to be able to complete a tasker that may pop up in the middle of the scrub is crucial.”

Although Jones headed the innovative process, he had the help of several wing innovation team members and UTRs to create this tool. The team has members in various career fields across the installation, bringing diverse viewpoints to identify and solve problems.

“The Wing Innovation Team was involved,” Jones said. “I said I was working on this and I could really use someone to write instructions from a normal person's perspective, not a UTR's. Staff Sgt. Dashawn Davis volunteered [to write them]. Senior Airman Chase Miller helped beta test it for me, [along with several other UTRs]. Lt. Col. Joseph Citro and Capt. Shawn Ripley helped get us in front of [9th Air Force (Air Forces Central)] to talk to them. Master Sgt. Shawn Warwick, 386th Expeditionary Logistics Readiness Squadron logistics plans superintendent, knows how everything is supposed to be done, so I sat down with him for two hours.

“This was definitely a team thing,” Jones added. “My commander was pushing innovation from the beginning, and leadership was one of the many linchpins in making it successful.”

Some wish to see this macro be utilized on a larger scale because this product saves so much time while ensuring an accurate Secretary of Defense directed personnel turnover.

“I would like to see this product reach other hubs and further main operating bases within the [area of responsibility],” said Warwick. “This would help alleviate hours spent on this necessary mandated [scrub] and allow more time spent on other mission critical aspects of deployments.”

Other bases within the area of responsibility are exploring the value such innovation provides; but it goes beyond the product itself and into how wings empower Airmen to work toward a common goal: making their vision a reality.

“We provide a space where members can receive training on innovation strategies as well gather together to identify possible issues as well as solutions,” said Ripley, the Wing Innovation Team lead. “The Wing Innovation Team is critical because it provides mentorship and support to all of those amazing Airmen we have at [Ali Al Salem Air Base] who know a better way to do things.”