We have some contractors who email us their hours in various formats. E.g.,
March 5, 2020, 7:21pm-7:59pm: prepping ingredients for pie
3/6: 11:30am to 12:30pm, (prep and cooking).
Wednesday, 3/4 taste test(3 hours)
I built a doc that extracts the date and minutes worked and automatically logs them in a master timesheet:
Here’s the formula to extract a date, which works for the date formats I see:
RegexReplace(thisRow.[Double click and paste 1 time entry per row, or write it],"[\s\S]*?\b((([1-9][0-2]?)/([0-9]?[1-9]))|((Jan|January|Feb|February|Mar|March|Apr|April|May|Jun|June|Jul|July|Aug|August|Sep|September|Oct|October|Nov|November|Dec|December) [0-9]?[1-9]))[\s\S]*","$1").ToDate()
Here's a formula to extract a begin time:
RegexReplace(thisRow.[Double click and paste 1 time entry per row, or write it],"[\s\S]*?((1?[0-9](am|AM|pm|PM))|(1?[0-9]:[0-9][0-9]))[\s\S]*","$1").ToTime()
RegexReplace(thisRow.[Double click and paste 1 time entry per row, or write it],"[\s\S]*?(?<=(?:to\s?|-\s?))([01]?\d:?\d?\d?\s?[pPaA]?)[\s\S]*","$1").ToTime()
These can’t handle everything, so if you’re a RegEx hero, I’d love your input!