# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor II

## Calculate Timestamp

Hi All,

I need to calculate the time difference. I have following table

 TR Registered Date Triggering Date 1 Oct 1, 2018 11:07:59 PM Oct 2, 2018 5:00:00 PM 2 Oct 1, 2018 11:08:03 PM Oct 2, 2018 5:00:00 PM 3 Oct 1, 2018 11:08:02 PM Oct 2, 2018 5:00:00 PM 4 Sep 28, 2018 11:08:03 AM Sep 28, 2018 5:00:00 PM 5 Sep 28, 2018 11:08:03 AM Sep 28, 2018 5:00:00 PM 6 Sep 28, 2018 11:08:03 AM Sep 28, 2018 5:00:00 PM 7 Sep 28, 2018 11:08:06 PM Oct 1, 2018 5:00:00 PM 8 Sep 28, 2018 11:08:06 PM Oct 1, 2018 5:00:00 PM 9 Sep 28, 2018 11:08:06 PM Oct 1, 2018 5:00:00 PM 10 Sep 28, 2018 11:08:06 PM Oct 1, 2018 5:00:00 PM

I have field "Registered Date" and according to time stamp of it i need to have Triggering Date. The calculation scenario is

1.If my Registered Date time is before the 12:00:00 PM then it should have Triggering Date of same day but at 05:00:00 PM

2. If my Registered Date time is after the 12:00:00 PM then it should have Triggering Date of next day at 05:00:00 PM

3. If my day is Friday and time is after 12:00:00 PM then it should have manual hold of next Monday 05:00:00 PM in this case in above table 28th Sep 2018 is Friday and time is after 12:00:00 PM so Triggering Date is 1st Oct 2018 05:00:00 PM.

How can i achieve this?

1 Solution

Accepted Solutions
MVP

## Re: Calculate Timestamp

```"TR Auto Hold Date/Time (local)",
TimeStamp(If(Frac("TR Auto Hold Date/Time (local)") < 0.5,
Floor("TR Auto Hold Date/Time (local)") + MakeTime (17),
LastWorkDate(Floor("TR Auto Hold Date/Time (local)"), 2) + MakeTime(17)), 'MM/DD/YYYY hh:mm:ss TT') as "Triggering Date"```
6 Replies
MVP

## Re: Calculate Timestamp

Try this

```Table:
TimeStamp#([Registered Date], 'MMM D, YYYY hh:mm:ss TT') as [Registered Date],
TimeStamp(If(Frac(TimeStamp#([Registered Date], 'MMM D, YYYY hh:mm:ss TT')) < 0.5,
Floor(TimeStamp#([Registered Date], 'MMM D, YYYY hh:mm:ss TT')) + MakeTime (17),
LastWorkDate(Floor(TimeStamp#([Registered Date], 'MMM D, YYYY hh:mm:ss TT')), 2) + MakeTime(17)), 'MMM D, YYYY hh:mm:ss TT') as [Triggering Date];
TR, Registered Date
1, "Oct 1, 2018 11:07:59 PM"
2, "Oct 1, 2018 11:08:03 PM"
3, "Oct 1, 2018 11:08:02 PM"
4, "Sep 28, 2018 11:08:03 AM"
5, "Sep 28, 2018 11:08:03 AM"
6, "Sep 28, 2018 11:08:03 AM"
7, "Sep 28, 2018 11:08:06 PM"
8, "Sep 28, 2018 11:08:06 PM"
9, "Sep 28, 2018 11:08:06 PM"
10, "Sep 28, 2018 11:08:06 PM"
];```
New Contributor II

## Re: Calculate Timestamp

Hi Sunny,

But my date format is "M/D/YYYY  hh:mm:ss " which i changed in your expression but still it is showing null values.

MVP

## Re: Calculate Timestamp

Not exactly sure what you have done incorrectly, can you share an image of the script and the date field so that I can see the format?

New Contributor II

## Re: Calculate Timestamp

Hi,

This is the script modified by me:

TimeStamp#("TR Auto Hold Date/Time (local)", 'MM/DD/YYYY hh:mm:ss TT') as "TR Auto Hold Date/Time (local)",
TimeStamp(If(Frac(TimeStamp#("TR Auto Hold Date/Time (local)", 'MM/DD/YYYY hh:mm:ss TT')) < 0.5,
Floor(TimeStamp#("TR Auto Hold Date/Time (local)", 'MM/DD/YYYY hh:mm:ss TT')) + MakeTime (17),
LastWorkDate(Floor(TimeStamp#("TR Auto Hold Date/Time (local)", 'MM/DD/YYYY hh:mm:ss TT')), 2) + MakeTime(17)), 'MM/DD/YYYY hh:mm:ss TT') as "Triggering Date"

Screenshot is also attached for format.

MVP

## Re: Calculate Timestamp

```"TR Auto Hold Date/Time (local)",
TimeStamp(If(Frac("TR Auto Hold Date/Time (local)") < 0.5,
Floor("TR Auto Hold Date/Time (local)") + MakeTime (17),
LastWorkDate(Floor("TR Auto Hold Date/Time (local)"), 2) + MakeTime(17)), 'MM/DD/YYYY hh:mm:ss TT') as "Triggering Date"```
New Contributor II

Hi Sunny,