Help checking for duplicate text, pulling some text from different lines, and combining into new line

Questions & Suggestions
1

Still stumbling (happily :slight_smile: !) through the basics of regex and currently struggling. I have the month's work schedule in excel, and wrote a macro that crawls through it pulling the dates and times I am working, like so:

08/29/16 7:30am - 2:30pm Calendar Work 08/30/16 7:30am - 2:30pm Calendar Work 08/31/16 9:30am - 5:00pm Calendar Work 08/31/16 1:00pm - 8:00pm Calendar Work 09/02/16 7:30am - 2:30pm Calendar Work

I have a second macro that uses this text to enter the shifts into Fantastical 2, my preferred calendar app.

There are some days where I work longer shifts, which are written in to the calendar separately, like on 08/31/16 above, seen here:

Currently, my macro for Fantastical 2 enters this as two overlapping shifts, which I go back later to manually correct. I'd like to fix this, because the whole reason I wrote the macros in the first place was to alleviate anxiety over having copied my schedule wrong manually... but I'm still doing things manually, so it isn't done yet!

I'd like to take those two lines:

Line 1: 08/31/16 9:30am - 5:00pm Calendar Work
Line 2: 08/31/16 1:00pm - 8:00pm Calendar Work

And make a new line:

Line 3: 08/31/16 9:30am - 8:00pm Calendar Work

Clarification: I do not need to check and see if the times are overlapping, I only need to find a way to take the start time from Line 1, and the end time from Line 2. I know they are overlapping shifts because the dates are the same.

I think what I have to do is use regex to check each line against the last, and if the dates match (\d\d\/\d\d\/\d\d), then I need to take the first time group (\d:\d\dam) from line 1, and the last time group from the line 2. (\d:\d\dpm), and use those groups to create line 3.

During this, I need to NOT enter Line 1, and Line 2 into Fantastical, and probably create a second list of exceptional shifts (all of the various "Line 3's") and circle back to enter them after all of the regular shifts have been entered.

I think I need a lot of help, possibly more than I can get from the kindness of experts... but I also think I'm overthinking it, and it might be easier than it seems. :blush: Sorry to go into such detail.

Here are the macros I am using, and an excel file with an example schedule:

scheduleExcelExample.zip (39.1 KB)
[SCH] A. Pull List of Work Shifts.kmmacros (12.9 KB)
[SCH] B. Add Work Shifts to Fantastical 2.kmmacros (9.0 KB)
[SCH]~Check Name (SUB).kmmacros (12.9 KB)

Thank you for any help!

2

To me this looks like almost impossible stuff for a regex. Probably a more adequate tool for this kind of calculations would be a database program, or Excel.

You could convert the text to CSV (08/31/16,9:30am,5:00pm,Calendar Work), import it in Excel and doing the math there. (I you are good at that.)

3

Thank you, I have updated the title to reflect that Regex may not be the right tool. I am slightly better with Excel than regex or KM.

An alternative method occurred to me, that I might be able to do the checking as I grab each date from the schedule spreadsheet. I’ll have to do some Variable juggling and If/Then-ing to make it work… if it could work that is.

4

Sounds slightly more promising :wink: But you still have to do calculations:

The day must be the same, if Yes, you have to check that the time spans are really overlapping, and then you have to combine the earliest start time with the latest end time.

5

Calculating overlapped events is not the simplest thing to do, and it’s not intuitive (at least to me it isn’t).

If you’re going to try it in Excel, make sure you Google it and find and example that works. Trying to figure it out on your own can be hard.

Then again, maybe it was just hard for me, the half-dozen times I’ve done it in the past - all of which were far enough apart that I managed to forget everything I learned in the past. :rolling_eyes:

6

My thought is to take the day, start, and end times as separate variables (if possible), and then if the days are the same, take the start time from the first shift, and the end time from the second shift, and create the new line and go from there. I don’t need to worry about checking if they actually overlap, because if my name appears twice the same day because of how the calendar is structured.

7

[quote=“DanThomas, post:5, topic:4925, full:true”]
Calculating overlapped events is not the simplest thing to do, and it’s not intuitive (at least to me it isn’t).[/quote]

I wasn’t clear in my opening post. I don’t need to calculate overlapping, because I structured the excel sheet in such a way that if the name appears twice, we call it a “double.” Also, I write the schedule. If there are two lines with the same date, it is a double. Now I just need to turn two lines with overlapping times into one line. I don’t even need to know which is which, because the first line is always the earlier shift, so I take the start time from it, and the second line is always the later shift, so I can take the end time from it.

I think I am on to something with the If/Then checking. I will just need to do quite a bit of juggling with the Variables.

8

The good thing is that you don’t seem to have shifts that start before midnight and end after midnight…

10

Not anymore! :slight_smile:

1 Like
11

I think I'm on the right track, but I'm missing something... somewhere.

I've decided to stick with the method I've already got for pulling out all of the shifts, and then to run through the text list of shifts and check for "doubles" there. This is the macro I'm using so far (with dummy Set Variables at the top, for testing purposes) but it's not working and my brain is no longer fresh. Does anything stick out to y'all?

Fix Doubles From Schedule.kmmacros (11.4 KB)

blob.jpg733Ă—3227 587 KB

And this is the output I'm getting from the Display Text Actions:

blob.jpg1105Ă—431 84.5 KB

12

Realized I’m not comparing date1 and date2 properly. Think it needs to be a calculation test and that I’ll need to convert date1 and date2 into another format somehow to check if date1-date2=0.

… Or something :slight_smile:

Posting from my phone so have not been able to try this yet.

13

Well, if I remember correctly (and I may not), date ranges overlap when, assuming date1 is less than date2:

if date1.start < date2.end and date1.end > date2.start

I think that’s right…

14

No, if it’s only about the day, this isn’t necessary. But the date1 variable isn’t formatted correctly in your If…Then test. It should be like this:

With that you already get the second line correct in the output.

15

Great! Thank you! Now that I’ve got rewriting the line down, another problem has sprung up.

matchList:

  A 08/31/16 8:30am - 5:00pm Calendar Work
  B 08/31/16 1:00pm - 8:00pm Calendar Work
  C 09/02/16 7:30am - 2:30pm Calendar Work
  D 09/03/16 1:00pm - 8:00pm Calendar Work


newMatchList:

  A 08/31/16 8:30am - 5:00pm Calendar Work
  X 08/31/16 7:30am - 8:00pm Calendar Work
  C 09/02/16 7:30am - 2:30pm Calendar Work
  D 09/03/16 1:00pm - 8:00pm Calendar Work

As it stands I am checking A against nothing, so it is added to newMatchList. Then we check B against A, which triggers the creation of X… But we’ve already written A to the newMatchList!

Do I need a third hopper (line1, line2, line3) to prevent this?

16

Bevor you go on, I think something is wrong with your current version of the macro.

With the change from above I’m getting this result:

newMatchList
08/31/16 9:30am - 5:00pm Calendar Work
08/31/16 9:30am - 8:00pm Calendar Work
09/02/16 7:30am - 2:30pm Calendar Work
09/03/16 1:00pm - 8:00pm Calendar Work

from that input:

08/31/16 9:30am - 5:00pm Calendar Work
08/31/16 1:00pm - 8:00pm Calendar Work
09/02/16 7:30am - 2:30pm Calendar Work
09/03/16 1:00pm - 8:00pm Calendar Work

Line 2 is correct, which is not the case in your version.

BTW, I would add some more sample lines for the tests; with another double in the middle.

17

I've been using a longer list to test, I just didn't want to muddy up the post. Here is a fresh set of info and the current macro I am using:

Fix Doubles From Schedule v2.kmmacros (12.1 KB)

blob.jpg733Ă—2741 476 KB

Which produces this output (I've added the lettering)

matchList:

A 09/01/16 7:30am - 2:30pm Calendar Work
B 09/01/16 1:00pm - 8:00pm Calendar Work
C 09/02/16 7:30am - 2:30pm Calendar Work
D 09/02/16 1:00pm - 8:00pm Calendar Work
E 09/03/16 7:30am - 2:30pm Calendar Work
F 09/04/16 7:30am - 2:30pm Calendar Work
G 09/04/16 1:00pm - 8:00pm Calendar Work


newMatchList:

A 09/01/16 7:30am - 2:30pm Calendar Work
X 09/01/16 7:30am - 8:00pm Calendar Work
B 09/02/16 7:30am - 2:30pm Calendar Work
Y 09/02/16 7:30am - 8:00pm Calendar Work
E 09/03/16 7:30am - 2:30pm Calendar Work
F 09/04/16 7:30am - 2:30pm Calendar Work
Z 09/04/16 7:30am - 8:00pm Calendar Work

What I'm looking for is:

newMatchList:

X 09/01/16 7:30am - 8:00pm Calendar Work
Y 09/02/16 7:30am - 8:00pm Calendar Work
E 09/03/16 7:30am - 2:30pm Calendar Work
Z 09/04/16 7:30am - 8:00pm Calendar Work

So the method we're using to create X Y and Z is working, BUT I'm still passing A B C D E F G through to newMatchList.

As I said above I think this is because I am checking A against nothing, so it is added to newMatchList. Then we check B against A, which triggers the creation of X.... But we've already written A to the newMatchList! And it continues through the rest of the list.

Do I need a third hopper (line1, line2, line3) to prevent this?

P.S.: sorry for changing terminology and data sets throughout the post. I didn't have clear terms for myself before posting originally.

18

You can eliminate duplicate dates afterwards, with something like this:

Delete Dupe Dates.kmmacros (1.8 KB)

Found the awk script here.

19

Yep, it works here. When I combine the two macros I get this:

09/01/16 7:30am - 8:00pm Calendar Work
09/02/16 7:30am - 8:00pm Calendar Work
09/03/16 7:30am - 2:30pm Calendar Work
09/04/16 7:30am - 8:00pm Calendar Work

which seems to be correct.

20

yes, it does! I am tinkering now and will run it through my real schedule, and import it again to make sure i get a 1:1 match with what I already have in there. Will update afterwards (but I should really get some profitable work done first!)

21

Thank you, it does indeed work perfectly now. I can finally stop worrying about whether or not I entered my schedule correctly.