Convert 12 hour time to 24 hour time

DarthData · Feb 2, 2020, 9:47 AM

I have looked at the other posts on here (https://community.notepad-plus-plus.org/topic/16854/convert-24-hour-military-time-to-12-hour-time) or (https://community.notepad-plus-plus.org/topic/17649/12-hour-to-24-hour-clock-conversion/20) and am not able to find what I want. With the given time formats:
05:23 am
05:23 AM
05:23 pm
05:23 PM
5:23:20 am
5:23:20 AM
5:23:20 pm
05:23:20 PM
12:01 am
12:01 AM
12:01 pm
12:01 PM
12:01:30 am
12:01:30 pm
12:01:30 AM
12:01:30 PM

I ultimately want to be able to sort them in order of time either ascending or descending with any combination of the above formats. Even if mixed. My thought was to convert them to military time, sort, then convert back in a defined format such as hh:mm:ss AM or PM. If seconds are not available in source time, ie… 12:01 PM, I would want to make the final number be something like: 12:01:00 PM or add three spaces in the front, so that the times line up like this:
12:01:30 PM
12:01 PM
This is all going to be part of a macro, so, multiple steps are ok. I can use either of these to select any of my times, but, I am not able to make the conversion or replacement with regex:
(?:(1)|(2)|(3)|(4)|(5)|(6)|(7)|(8)|(9)|(01)|(02)|(03)|(04)|(05)|(06)|(07)|(08)|(09)|(10)|(11)|(12))(:[0-5][0-9])(:[0-5][0-9])?(.?(A|P)M)
or
(?:([0-2]?[0-9]))(:[0-5][0-9])(:[0-5][0-9])?(.?(A|P)M)
Thanks in advance.

guy038 · Feb 2, 2020, 2:47 PM

Hello, @victor-fansler, and All,

Of course, there is a regex solution !

We should agree, first, with some conversion rules :

I suppose, for instance, that the following table is OK :

    Military hour |  AM/PM hour
    --------------•---------------
       01:07      |   01:07 am
       03:17      |   03:17 am
       05:23      |   05:23 am
       07:31      |   07:31 am
       09:43      |   09:43 am
       11:57      |   11:57 am
       12:00      |   12:00 pm
       14:04      |   02:04 pm
       16:15      |   04:15 pm
       18:22      |   06:22 pm
       20:33      |   08:33 pm
       22:45      |   10:45 pm
       23:59      |   11:59 pm
    ------------------------------

However, what’s about these two specific forms :

    Military hour |            AM/PM hour
    --------------•---------------------------------
       00:00      |   00:00 am    OR    12:00 am  
       00:04      |   00:04 am    OR    12:04 am  
    ------------------------------------------------

Which time format are you using ?

I’ve already built the suitable regexes to get a correct sort and the wanted output template, i.e. the cases :

12:34 AM , 12:34 PM , 12:34:56 AM , 12:34:56 PM, all preceded with 3 space characters

See you later

Best Regards

guy038

DarthData · Feb 2, 2020, 4:32 PM

Hey @guy038 that is the general idea of what I am looking for, however, I need to have seconds accounted for. as for 12 hr time, I need 12:04:00 AM format. My final product will always be in 12:04:00 AM format sorted bottom to top. None of my sources are in military time, my only reason for involving it is to sort, so, as long as they can sort correctly I need the following: Yes, I am adding date now… I realized I need to span over a few days. I don’t think date format will change, however someone else reading this may want it in “proper” format. So, I am ok with final date looking like 02/01/2020.

Source Times:
2/1/2020 1:07:23 am
2/1/2020 3:17AM
2/1/2020 05:23:52 am
2/1/2020 7:31 AM
2/2/2020 12:04:22 AM
2/2/2020 09:43 am
2/1/2020 11:57:17 am
2/2/2020 12:00 PM
2/1/2020 2:04:10 pm
2/1/2020 4:15PM
2/1/2020 06:22 pm

Ending Times: 
Note that if source did not have seconds, final result does not.
Note that if source did not have a leading 0, the final result does.
Note that final result is in UPPERCASE.
2/2/2020 12:00 PM
2/2/2020 09:43 AM
2/2/2020 12:04:22 AM
2/1/2020 06:22 PM
2/1/2020 04:15 PM
2/1/2020 02:04:10 PM
2/1/2020 11:57:17 AM
2/1/2020 07:31 AM
2/1/2020 05:23:52 AM
2/1/2020 03:17 AM
2/1/2020 01:07:23 AM

I hope I have not complicated this too much. I did not include date before, because if needed I could manipulate them later. Basically, the source is from multiple email’s from PRTG that I copy/paste into Notepad++ run it through a macro and then output a new email with summarized results. I have already collected the other information from source email (Device/IP, Customer, Location, Date/Times and Status “Up” or “Down”). Unfortunately, I do not have control over the source format in PRTG, but it is fairly consistent. I was told, I could run it through a python script and give that too them, but I don’t have enough information to even attempt that right now. So, regex/macro’s it is… Yes, my shortcuts.xml file is “HUGE”.

Thanks for your assistance,
Victor

guy038 · Feb 2, 2020, 8:41 PM

Hi, @victor-fansler, and All,

Up to now, I understood that :

Any hour like HH:MM remains with this format, in the output file ( So you do not want to add :00 seconds )
Any hour with the H:MM[:SS] format, will be rewritten 0H:MM[:SS] in the output file
Any hour ending the the forms am, Am or aM are changed into AM in the output file and idem for the pm syntaxes

But I need further clarifications ! You did not answer my initial question, and, moreover, you includes date

Firstly, may your input text contain hours like 12:mm[:ss] AM ( hours after midnight and before 01:00 AM ) or does it always contain hours as 00:mm[:ss] AM ? In this specific case, what output do you expect :12:mm:[ss] AM OR 00:mm:[ss] AM ?
Secondly, do your dates always follow the English format, Month/Day/Year or are like French dates Day/Month/Year or else ?
Thirdly, in case of a leading 0 is missing in the day and/or the month, do you expect the output text to contain this leading 0, too ? ( So, for instance, date X/Y/2020 would changed into 0X/0Y/2020 )
Fourthly, does the year always occurs with 4 digits ( 2020 ), with 2 digits ( 20 ), or with the both syntaxes and, of course, which year output do you expect ?
Fifthly, do you have some dates without the hour part or, the opposite, are there some hours without the date part ?

Best regards,

guy038

DarthData · Feb 3, 2020, 5:39 AM

@guy038,
Please see below for your answers.

Any hour like HH:MM remains with this format, in the output file ( So you do not want to add :00 seconds )
Answer: If an email does not have seconds, do not add it.

Any hour with the H:MM[:SS] format, will be rewritten 0H:MM[:SS] in the output file
Answer: Correct add the leading zero

Any hour ending the the forms am, Am or aM are changed into AM in the output file and idem for the pm syntaxes
Answer: Correct, change am/pm to uppercase AM/PM

But I need further clarifications ! You did not answer my initial question, and, moreover, you includes date
Answer: I am not sure what else you needed. I think it is all covered in other questions.

Firstly, may your input text contain hours like 12:mm[:ss] AM ( hours after midnight and before 01:00 AM ) or does it always contain hours as 00:mm[:ss] AM ? In this specific case, what output do you expect :12:mm:[ss] AM OR 00:mm:[ss] AM ?
Answer: input will always be in 12:mm[:ss] AM format for the morning. I have not seen any with 00:mm[:ss] AM format.

Secondly, do your dates always follow the English format, Month/Day/Year or are like French dates Day/Month/Year or else ?
Answer: Month/Day/Year or Mmm dd ie… (Jan 10) No year given, but is current year. This second format is less common, and can be manipulated separately if needed. If to complicated, just ignore that format.

Thirdly, in case of a leading 0 is missing in the day and/or the month, do you expect the output text to contain this leading 0, too ? ( So, for instance, date X/Y/2020 would changed into 0X/0Y/2020 )
Answer: Yes, add the leading 0’s

Fourthly, does the year always occurs with 4 digits ( 2020 ), with 2 digits ( 20 ), or with the both syntaxes and, of course, which year output do you expect ?
Answer: Yes, 4 digit years

Fifthly, do you have some dates without the hour part or, the opposite, are there some hours without the date part ?
Answer: These are the formats that will be given: so, yes, there will be some times without the date, however these are received on the same day of even, so, we can add current date to them.
2/2/2020 6:23:42 AM
2/2/2020 6:54:41 AM
2/1/2020 12:25:35 PM
1/31/2020 11:36:11 PM
11:14 AM
10:40 AM
12:34 AM
08:06 PM
3:45 PM

Thanks for your help,
Victor

guy038 · Feb 3, 2020, 9:40 AM

Hi, @victor-fansler, and All,

Sorry to bother you again ! But, but how do I know your data and how it is arranged ! So,

Does your [date and] hour format simply begins each line, of an input text, with possible leading blank chars

OR

Is the [date and] hour format just one field of each line, made of several fields, separated with a delimiter ( a comma, a tabulation or else… ) ?

In this later case, you could, also, e-mail me some parts of your input file, if not personal nor confidential to my e-mail address :

You said, too :

so yes, there will be some times without the date, however these are received on the same day of even, so, we can add current date to them.

I’m afraid that adding current date is beyond possibilities of regular expressions. I mean, a regular expression can detect if a date is missing, before the hour part but, in replacement, you’ll need to, manually, type in the current date, in order that any hour HH:MM[:SS] AM|PM, without the leading date, will be changed as 02/03/2020 HH:MM[:SS] AM|PM !

If you want this to be done automatically, you obviously need a script language ( Python, Lua, Perl, AWK, … )

But…, wait a minute ! We could use a work-around replacing any missing date with, for instance, the literal MM/DD/YYYY syntax. As, obviously, no date can be greater than current one, after sort, the lines, containing MM/DD/YYYY would be , automatically, grouped at the end of the file, for easy further processing !

BR

guy038

DarthData · Feb 3, 2020, 11:51 AM

@guy038 I am ok with pointing out the ones with missing dates by inserting a placeholder of the literal MM/DD/YYYY. Then, I would know to update those, and can always manually sort those lines afterwards.

guy038 · Feb 3, 2020, 9:51 PM

Hello, @victor-fansler, and All,

You did not answer to my question, at the very beginning of my previous post. So I’ll suppose the first case, i.e. :

[Date +] hour are listed, one per line, with possible leading blank characters

So, assuming this test file, below, containing an unsorted list of [dates+] hours, pasted in a new tab, where :

Tabulation and/or space char(s) may begin each line
Tabulation and/or space char(s) separates date from hour
Tabulation and/or space char(s) separates hour from am/pm or are absent
The parts month, day and hour may contain one digit only
The seconds part and/or all the date part may be absent

9/6/2019 12:00 am
03/08/2020		12:04 am
1:07am
  7/27/2020     03:17:25 am
05/10/2019 5:23AM
	03/08/2020 01:30:42 AM
12/13/2020 07:31:09AM
    01/22/2019 9:43AM
06/5/2020 	11:57:43 AM
10/09/2019 12:00 pm
09/06/2019 03:23:51am
	07/14/2020 02:04:22am
10/29/2019 4:15 pm
  11/20/2020 06:22:08PM
	2/8/2019 8:33 PM
10:45PM
05/01/2019 			11:59:00 PM
09/06/2019 06:22:08PM
	9:43AM
11/30/2019 12:04 am
    7/27/2020 		10:45PM
05/10/2019 03:17:25 am
12/25/2020 5:23AM
11/30/2019 		08:59:11 am
  1/1/2019    12:00 pm
06/08/2020 07:31:09AM
10/9/2019 11:57:43 AM
    07/14/2020 		02:04:22pm
10/29/2019 11:15 am
11/20/2020 12:00:51am
11:59:00 PM
	04/15/2020	 8:33 PM
	11/20/2020 2:00:51AM
05/01/2019 1:07am

After running the following regex S/R :

SEARCH ^\h*(((\d)?\d)/((\d)?\d)/(\d\d\d\d)\h+)?((12)|(\d)?\d|):(\d\d)(:\d\d)?\h*((?i)(am)|pm)
REPLACE (?1\6/(?3:0)\2/(?5:0)\4:Curr.\x20Date)\x20\U$12\E\x20(?8(?{13}00:12):(?9:0)\7):$10$11\t\t\t\t(?1(?3:0)\2/(?5:0)\4/\6:Curr.\x20Date)\x20(?{8}:(?9:0))\7:$10$11\x20\U$12

with the Wrap around option ticked and the Regular expression selected

We get the new text, below, where you can notice two parts, separated with 4 tabulation characters

The last part are the final date and hour re-written, according to the common rules, mentioned in the previous posts
The first part, added by the regex S/R, is the key for a correct sort of dates and hours. Indeed, you need the Year/Month/Day AM/PM Hour/Minutes/Seconds syntax. Moreover, if we have, for instance, AM 12:04, we must change it, in the first part, as AM 00:04 in order to be sorted before, for instance, the value AM 05:24:11, for a same date !

2019/09/06 AM 00:00				09/06/2019 12:00 AM
2020/03/08 AM 00:04				03/08/2020 12:04 AM
Curr. Date AM 01:07				Curr. Date 01:07 AM
2020/07/27 AM 03:17:25				07/27/2020 03:17:25 AM
2019/05/10 AM 05:23				05/10/2019 05:23 AM
2020/03/08 AM 01:30:42				03/08/2020 01:30:42 AM
2020/12/13 AM 07:31:09				12/13/2020 07:31:09 AM
2019/01/22 AM 09:43				01/22/2019 09:43 AM
2020/06/05 AM 11:57:43				06/05/2020 11:57:43 AM
2019/10/09 PM 12:00				10/09/2019 12:00 PM
2019/09/06 AM 03:23:51				09/06/2019 03:23:51 AM
2020/07/14 AM 02:04:22				07/14/2020 02:04:22 AM
2019/10/29 PM 04:15				10/29/2019 04:15 PM
2020/11/20 PM 06:22:08				11/20/2020 06:22:08 PM
2019/02/08 PM 08:33				02/08/2019 08:33 PM
Curr. Date PM 10:45				Curr. Date 10:45 PM
2019/05/01 PM 11:59:00				05/01/2019 11:59:00 PM
2019/09/06 PM 06:22:08				09/06/2019 06:22:08 PM
Curr. Date AM 09:43				Curr. Date 09:43 AM
2019/11/30 AM 00:04				11/30/2019 12:04 AM
2020/07/27 PM 10:45				07/27/2020 10:45 PM
2019/05/10 AM 03:17:25				05/10/2019 03:17:25 AM
2020/12/25 AM 05:23				12/25/2020 05:23 AM
2019/11/30 AM 08:59:11				11/30/2019 08:59:11 AM
2019/01/01 PM 12:00				01/01/2019 12:00 PM
2020/06/08 AM 07:31:09				06/08/2020 07:31:09 AM
2019/10/09 AM 11:57:43				10/09/2019 11:57:43 AM
2020/07/14 PM 02:04:22				07/14/2020 02:04:22 PM
2019/10/29 AM 11:15				10/29/2019 11:15 AM
2020/11/20 AM 00:00:51				11/20/2020 12:00:51 AM
Curr. Date PM 11:59:00				Curr. Date 11:59:00 PM
2020/04/15 PM 08:33				04/15/2020 08:33 PM
2020/11/20 AM 02:00:51				11/20/2020 02:00:51 AM
2019/05/01 AM 01:07				05/01/2019 01:07 AM

Now, we simply run the usual Unicode sort, with the command Edit > Line Operations > Sort lines Lexicographically Ascending, giving :

2019/01/01 PM 12:00				01/01/2019 12:00 PM
2019/01/22 AM 09:43				01/22/2019 09:43 AM
2019/02/08 PM 08:33				02/08/2019 08:33 PM
2019/05/01 AM 01:07				05/01/2019 01:07 AM
2019/05/01 PM 11:59:00				05/01/2019 11:59:00 PM
2019/05/10 AM 03:17:25				05/10/2019 03:17:25 AM
2019/05/10 AM 05:23				05/10/2019 05:23 AM
2019/09/06 AM 00:00				09/06/2019 12:00 AM
2019/09/06 AM 03:23:51				09/06/2019 03:23:51 AM
2019/09/06 PM 06:22:08				09/06/2019 06:22:08 PM
2019/10/09 AM 11:57:43				10/09/2019 11:57:43 AM
2019/10/09 PM 12:00				10/09/2019 12:00 PM
2019/10/29 AM 11:15				10/29/2019 11:15 AM
2019/10/29 PM 04:15				10/29/2019 04:15 PM
2019/11/30 AM 00:04				11/30/2019 12:04 AM
2019/11/30 AM 08:59:11				11/30/2019 08:59:11 AM
2020/03/08 AM 00:04				03/08/2020 12:04 AM
2020/03/08 AM 01:30:42				03/08/2020 01:30:42 AM
2020/04/15 PM 08:33				04/15/2020 08:33 PM
2020/06/05 AM 11:57:43				06/05/2020 11:57:43 AM
2020/06/08 AM 07:31:09				06/08/2020 07:31:09 AM
2020/07/14 AM 02:04:22				07/14/2020 02:04:22 AM
2020/07/14 PM 02:04:22				07/14/2020 02:04:22 PM
2020/07/27 AM 03:17:25				07/27/2020 03:17:25 AM
2020/07/27 PM 10:45				07/27/2020 10:45 PM
2020/11/20 AM 00:00:51				11/20/2020 12:00:51 AM
2020/11/20 AM 02:00:51				11/20/2020 02:00:51 AM
2020/11/20 PM 06:22:08				11/20/2020 06:22:08 PM
2020/12/13 AM 07:31:09				12/13/2020 07:31:09 AM
2020/12/25 AM 05:23				12/25/2020 05:23 AM
Curr. Date AM 01:07				Curr. Date 01:07 AM
Curr. Date AM 09:43				Curr. Date 09:43 AM
Curr. Date PM 10:45				Curr. Date 10:45 PM
Curr. Date PM 11:59:00				Curr. Date 11:59:00 PM

The remaining task to do is quite easy to guess ! We simply have to get rid of the sort key, at beginning of each line followed with the tabulation delimiters, as well

SEARCH (?-s)^.+\t+
REPLACE Leave EMPTY

We’re left with :

01/01/2019 12:00 PM
01/22/2019 09:43 AM
02/08/2019 08:33 PM
05/01/2019 01:07 AM
05/01/2019 11:59:00 PM
05/10/2019 03:17:25 AM
05/10/2019 05:23 AM
09/06/2019 12:00 AM
09/06/2019 03:23:51 AM
09/06/2019 06:22:08 PM
10/09/2019 11:57:43 AM
10/09/2019 12:00 PM
10/29/2019 11:15 AM
10/29/2019 04:15 PM
11/30/2019 12:04 AM
11/30/2019 08:59:11 AM
03/08/2020 12:04 AM
03/08/2020 01:30:42 AM
04/15/2020 08:33 PM
06/05/2020 11:57:43 AM
06/08/2020 07:31:09 AM
07/14/2020 02:04:22 AM
07/14/2020 02:04:22 PM
07/27/2020 03:17:25 AM
07/27/2020 10:45 PM
11/20/2020 12:00:51 AM
11/20/2020 02:00:51 AM
11/20/2020 06:22:08 PM
12/13/2020 07:31:09 AM
12/25/2020 05:23 AM
Curr. Date 01:07 AM
Curr. Date 09:43 AM
Curr. Date 10:45 PM
Curr. Date 11:59:00 PM

Et voilà ! Note that the last four lines, of our example, are initial lines without date, which are supposed to refer to the current date !

Next time, I’ll try to find the courage to give you some explanations on the regular expressions used !!

Best Regards,

guy038

Alan Kilborn · Feb 3, 2020, 10:10 PM

You did not answer my initial question

You did not answer to my question, at the very beginning of my previous post

Sensing a theme here.