?

[xyz1]

.

[jheasley]

Have you tried updating the number format on your field to 'MM hh:mm:ss'

[maxgro]

Using your 2 rows of test data

Logon:

load * Inline [

Account ,  Last Logon ,                      Last login Time,              Interval difference

0109222J ,    6/18/2015 12:55:40 PM ,  2/21/2017 6:34:59 PM  ,        614(days) or 20.467 Month

0109227R  ,  2/26/2017 1:00:15 PM    ,  2/27/2017 1:00:15 PM  ,        1(day) or 0.033 Month

];

Final:

load

  *,

  deltamonths > 2 as flag

  ;

load

  Account,

  [Last Logon],                    

  [Last login Time],

  Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as t1,

  Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as delta,

  Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt')) as deltadays,

  Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 as deltamonths

Resident Logon;

DROP Table Logon;

[xyz1]

.

[maxgro]

try to add a where filter on your data

maybe you have to change the format code, orange

.....

.....

FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE

      Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2

;   

[xyz1]

.

[maxgro · Accepted solution]

LOAD

    "Pre-W2K Name", (Account)

     if ("Pre-W2K Name"  like 'User*' , 'USER',

   if("Pre-W2K Name" like 'ADM*' , 'ADM',

    if("Pre-W2K Name" like 'BAL*' , 'BAL',

     if("Pre-W2K Name" like 'GEN*' , 'GEN',

      if("Pre-W2K Name" like 'VC*' , 'VC',

      if("Pre-W2K Name" like 'ADP*' , 'ADP',

      if("Pre-W2K Name" like 'ROOM_*' , 'Room',

       if("Pre-W2K Name" like 'FORMATION*' , 'FORMATION',

   if("Pre-W2K Name" like 'SVC*', 'SVC'))))))))) as ACCOUNTGROUP,

     if("Role" like 'DISABLED' , 'DISABLED',

   if("Role" like 'DISABLED\NO_PWDEXP' , 'Disabled and Expired Password',

    if("Role" like 'DISABLED\NO_PWDEXP\NO_PWDREQD' , 'Disabled, Password Expired and no password required',

     if("Role" like 'NO_PWDEXP' , 'No Password Expired',

      if("Role" like 'NO_PWDEXP\ENCRYPT' , 'Encrypted',

        if("Role" like 'NO_PWDEXP\NO_PWDREQD' , 'No Password',

        if("Role" like 'NO_PWDEXP\SMART' , 'Smart',

   if("Role" like 'NO_PWDEXP\DELEGATE', 'Delegated')))))))) as ACCOUNTSTATUS,

    "Display Name",

    "Last Logon",

    "Exhange Alias",

    "E-Mail",

       "Lync User",

    Role,

    DN,

    City,

    Today(),

    TextBetween( DN, 'CN=', ',') as [TypeName],

    TextBetween( DN, 'OU=', ',') as [SiteName],

    TextBetween( DN, 'DC=', ',') as DC,

    "Last login Time",

    Status

FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE

      Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2

;  

[xyz1]

.