Days pending from date with time

[#1]

Dear Friends

I have field creation date coming from sharepoint. The date is captured in below format.

7/15/2014 3:01 PM

I have tried several formulas to subract it from today() in the script. But it is not working.

Please suggest the way around.

Thanks

Rahul

[MK_QSL]

Convert your Date Field as Below

TimeStamp(Alt(
TimeStamp#(Date,'M/DD/YYYY hh:mm TT'),
Timestamp#(Date,'M/D/YYYY h:mm TT')
)) as Date

Now use below to find the difference in hour..

=TimeStamp(Now()-Date,'hh')

[#3]

Hi Manish

Thanks for your email.

I have enter the formula as suggested

TimeStamp(Alt(
  TimeStamp#([Created  ],'M/DD/YYYY hh:mm TT'),
  Timestamp#([Created  ],'M/D/YYYY h:mm TT')
)) as Creation_Date,

  TimeStamp(now()-(TimeStamp(Alt(
  TimeStamp#([Created  ],'M/DD/YYYY hh:mm TT'),
  Timestamp#([Created  ],'M/D/YYYY h:mm TT')
))),'dd') as pendingdays

However it is missing two days in the pending days

ISSUE.Creation_Date	ISSUE.pendingdays
15/07/2014   15:03:00	13
18/07/2014   10:51:00	11
21/07/2014   15:24:00	07

[MK_QSL]

Provide us your sample data file or apps..

[#5]

Hi Manish

Enclosed is the sample data file, it contains the ID and creation date.

I need to get how many days it is pending

so (today-creation date) as pending days

Thanks

Rahul

[Anonymous · Accepted solution]

Hi,

Something like below,

LOAD

  *,

  Interval(Now()-ISSUE.Created, 'hh') as Hours_Pending,

  Interval(Now()-ISSUE.Created, 'DD') as Days_Pending

;

LOAD

  ISSUE.ID,

  Timestamp(Date#(ISSUE.Created, 'M/D/YYYY h:mm TT')) as ISSUE.Created

;

LOAD * INLINE [

    ISSUE.ID, ISSUE.Created

    82, 7/15/2014 2:49 PM

    83, 7/15/2014 2:51 PM

    84, 7/15/2014 2:53 PM

    85, 7/15/2014 2:55 PM

    86, 7/15/2014 2:57 PM

    87, 7/15/2014 3:00 PM

    88, 7/15/2014 3:01 PM

    89, 7/15/2014 3:03 PM

    97, 7/18/2014 10:51 AM

    103, 7/21/2014 3:24 PM

];

[MK_QSL]

LOAD ID,

     Date as Date1,

     TimeStamp(Alt(

  TimeStamp#(Date,'M/DD/YYYY hh:mm TT'),

  Timestamp#(Date,'M/D/YYYY h:mm TT')

  )) as Date,

  Interval(NOW()-(TimeStamp(Alt(

  TimeStamp#(Date,'M/DD/YYYY hh:mm TT'),

  Timestamp#(Date,'M/D/YYYY h:mm TT')

  ))),'dd') as DifferenceinDays,

  Interval(NOW()-(TimeStamp(Alt(

  TimeStamp#(Date,'M/DD/YYYY hh:mm TT'),

  Timestamp#(Date,'M/D/YYYY h:mm TT')

  ))),'hh') as DifferenceinHours,

  Interval(NOW()-(TimeStamp(Alt(

  TimeStamp#(Date,'M/DD/YYYY hh:mm TT'),

  Timestamp#(Date,'M/D/YYYY h:mm TT')

  ))),'mm') as DifferenceinMinute

FROM TABLENAME

[#8]

Thanks Satyadev for your help.

[#9]

Thanks Manish for your help

[#10]

Dear Satyadev

I have to change the data source from list to XML that changed the date format.

Now it is coming as YYYY-MM-DD

I tried Interval(Now()-(Timestamp(Date#(ISSUE.Created, 'YYYY-MM-DD h:mm TT'))), 'DD') as Days_Pending

But it's not working, Do you know why?

2014-07-15   14:49:23

2014-07-15 14:51:29

2014-07-15 14:53:49

2014-07-15 14:55:35

2014-07-15 14:57:24

2014-07-15 15:00:09

2014-07-15 15:01:55

2014-07-15 15:03:20

2014-07-18 10:51:51

2014-07-21 15:24:08

2014-07-22 08:40:20

2014-07-22 08:44:49

2014-07-22 08:48:11

2014-07-22 08:52:04

2014-07-22 09:00:19

2014-07-22 09:01:51

2014-07-22 09:06:59

2014-07-22 09:09:30

2014-07-22 09:12:19

2014-07-22 09:13:53