Web Reports - Last Report Time - Remove the D$&% DAY!

Usage and Config
1

Please for the love of God I’m begging you give me a config option to set the date format in Last Report Time.
Excel cannot properly sort with this date format of “DDD, dd MMM yyyy” all because of the DDD.

Every day I run multiple reports, take the CSV, and put it into an Excel spreadsheet.
Every day, because Last Report Time starts with the three-letter day code (Sun, Mon, Tue, etc), I have to do Text to Columns on every report and click the teeeeeeny tiny lines between the date number and month, and between month and year, to give Excel a date it can sort.
image
And every day, every report, I have to delete the other columns after doing that because they’re irrelevant to the report.

image
image550×550 72.9 KB

I know I shouldn’t complain about easily justified hours… it’s easy money… but it’s driving me nuts.

2 Likes
2

I can relate to your frustration. I’ve created may own property to ensure the time is in a format I can use in Excel (specific to my locale settings in my OS) as this avoid all the hassle of dealing with the format of the Last Report Time time (I believe it uses a mime standard format). This way you can use US date format, European date format, / or - date separators etc

Q: (((day_of_month of it as two digits) & "/" & (month of it as two digits) & "/" & year of it as string) of date ("GMT" as time zone) of it & " " & ((two digit hour of it) & ":" & (two digit minute of it)) of time ("GMT" as time zone) of it) of (apparent registration server time)
A: 30/11/2021 15:54
T: 0.054 ms
5 Likes
3

Added this as a new Property, “Last Report Time (XLS)”. Life. Saver.

2 Likes
4

I’ve wondered about this date format because 1) it it’s uncommon, and 2) it’s used by nearly every component of BigFix. Since it’s used by everything, it’s probably not going away.

When I google that format, the closest approximation I can find is the 1989 IETF RFC 1123 international date format shown on a FreePascal Wiki page.

 DAY, DD MON YYYY hh:mm:ss GMT

e.g:

 Sun, 21 Oct 2018 12:16:24 GMT

Link: https://wiki.freepascal.org/RFC_1123_Time_Format

The main difference being the timezone, where here the date simply uses “GMT” at the end, the BigFix version uses the numeric timezone offset. When I check the RFC’s time format section, I see that numeric timezone format (like what BigFix uses) is allowed.

 There is a strong trend towards the use of numeric timezone
 indicators, and implementations SHOULD use numeric timezones
 instead of timezone names.  However, all implementations MUST
 accept either notation.  If timezone names are used, they MUST
 be exactly as defined in RFC-822.

Link: https://datatracker.ietf.org/doc/html/rfc1123#page-55

So it really is a longtime standard date format, only Excel doesn’t like it. That’s unfortunate and a little bizarre given Excel’s popularity and many other features. I don’t see Excel supporting it if it’s been this long already.

With the latest release of Excel, there is a new JavaScript API that could be useful for converting these to another format. My plate is full at the moment, but I want to dig in at some point. The JavaScript API could probably convert these really fast. MS adding GitHub integration for this is also really cool, so a solution should be easy to share.

Link: https://docs.microsoft.com/en-us/office/dev/add-ins/overview/explore-with-script-lab

It would also be cool if the CSV/Excel exports could simply use a converted date format. Looks like there is already an enhancement request for it.

Link: https://bigfix-ideas.hcltechsw.com/ideas/BFPTCH-I-104

There’s another one that’s really similar.

Link: https://bigfix-ideas.hcltechsw.com/ideas/BFP-I-97

I just now added my votes to them.

2 Likes
5

Nice legwork! Yeah, Excel isn’t budging if it’s been this long.

I modified @SLB’s suggestion slightly and it’s going well.

I also found that if you paste into another Excel spreadsheet and then hit CTRL, then V (but not CTRL+V again) it will paste and not clobber your column/cell formatting.

Between the two of these, my agitation is mostly resolved. :smiley:

1 Like