Text string with multiple dates formatted ?/??. Search for greatest difference between dates.

[rmgarrett]

Greetings,

Let's say I'm tracking my workouts at work. Suppose I had a chunk of text in a cell that looked like this:

8/30. Ran a mile around the building. 9/2. Treadmill. 9/19: Walked around the track. 9/20: Skipped jump rope in a fitness class.

The number of dates can vary within cells. I always use ?/?? format, but I can follow the date with a space, colon, or period.

I want to find the greatest amount of business days I went without working out at the company gym. How do I do this?

I can do

=SEARCH("?/??",A1,1) for the first date,
=SEARCH("?/??",A1,SEARCH("?/??",A1,1)+1) for the second date,

I could then use MID to capture the dates, DATE to turn them into dates with this calendar year, and NETWORKDAYS to get the difference. But how do I search for the *greatest* number of days?

I'd be grateful for any problem-solving assistance you can give me.

Thanks,
Rachel

 

[jimrward]

i would personally try and simplify the whole tracking, and opt for a worksheet with a date column, and an activity column, distance, time, and may be a notes column

Date | Activity |Distance |Time |notes
30/8 | Run |5| 00:40:12|Around Building
2/9 | Treadmill |6|00:47:02| Company Gym
19/9 | Walk | 3|00:45:00| Around Track

and then we can look at doing lookups and stats

 

[rmgarrett]

i would personally try and simplify the whole tracking, and opt for a worksheet with a date column, and an activity column, distance, time, and may be a notes column

Date | Activity |Distance |Time |notes
30/8 | Run |5| 00:40:12|Around Building
2/9 | Treadmill |6|00:47:02| Company Gym
19/9 | Walk | 3|00:45:00| Around Track

and then we can look at doing lookups and stats

No doubt your way is better. I was using a dummy example so I could share simplified data. The real situation is that this is call center data where agents are recording notes. There isn't a reconfiguration option. I'm just trying to save some time for the administrator who has to go in and examine these manually to see how long accounts sat before being handled.

Thanks!

 

[jimrward]

Hmmm, bit trickier then as you have no control over data entry, does your call handling system not have a reporting module, if not then date and time stamps in a column and maybe an action column, we could then look at flagging overdue items depending on SLA's etc etc within excel

 

[rmgarrett]

Hmmm, bit trickier then as you have no control over data entry, does your call handling system not have a reporting module, if not then date and time stamps in a column and maybe an action column, we could then look at flagging overdue items depending on SLA's etc etc within excel

Thanks for thinking about this. The system does have reporting functionality. We are focused on a very specific and narrow problem related to agents' own notes within their notes field.

I have the start of an approach as indicated above, but I don't know how to loop through the field and find the maximum number of elapsed days.

 

[jimrward]

Does your call logging system create a unique case ID which could also be added to another column, we could then get minimum date for a case and action and also the max date and subtract

 

[rmgarrett]

I am not part of the department in question. I was asked to take a look at this because I have been able to use Excel to help automate others' spreadsheet work in the past. If I can figure out an Excel formula that will parse these dates faster than a human can read them, that's all I'm trying to do.