# New to QlikView

Discussion board where members can get started with QlikView.

Contributor II

## Calculation on Date

Hi All,

I have a date filed called Breach Time in MM/DD/YYYY hh:mm:ss format.

Now I want below logic to be applied.

It will check if it is crossing 17:00:00 or not, if crossing then Qlik will check the difference and the difference will be added from 08:00:00 AM.

Example:
Case 1: 10-04-2018  15:34:32 --- will show same  10-04-2018  15:34:32.

Case 2: 10-04-2018  19:34:32 --- Will show           10-05-2018  10:34:32.

Can anyone help me to do this.

Thanks,

Sarif

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Calculation on Date

What is wrong if you get to the right result? I mean do you have a scenario where it doesn't match... from what I understood... if the time is 17:01 PM... you want to make it 8:01 AM the next day, right? 7 Hour from today and 8 hours from tomorrow make it 15 hours... what is wrong about this approach? May be I am missing something, but feel free to correct me.

I missed a square bracket after Breach Time within Hour... try this

TimeStamp(If(Hour([Breach Time]) > 17, [Breach Time] + MakeTime(15), [Breach Time])) as [Breach Time]

7 Replies
MVP

## Re: Calculation on Date

May be this

TimeStamp(If(Hour([Breach Time) > 17, [Breach Time] + MakeTime(15), [Breach Time])) as [Breach Time]

For example:

=TimeStamp(If(Hour(MakeDate(2018, 10, 4) + MakeTime(19, 34, 32)) > 17,

MakeDate(2018, 10, 4) + MakeTime(19, 34, 32) + MakeTime(15), MakeDate(2018, 10, 4) + MakeTime(19, 34, 32)))

Contributor II

## Re: Calculation on Date

Hi Sunny,

Above expression is throwing error saying hour takes 1 parameter.

and second thing putting 15 is right approach here?

TimeStamp(If(Hour([Breach Time) > 17, [Breach Time] + MakeTime(15), [Breach Time])) as [Breach Time]

Thanks,

Sarif

Highlighted
MVP

## Re: Calculation on Date

What is wrong if you get to the right result? I mean do you have a scenario where it doesn't match... from what I understood... if the time is 17:01 PM... you want to make it 8:01 AM the next day, right? 7 Hour from today and 8 hours from tomorrow make it 15 hours... what is wrong about this approach? May be I am missing something, but feel free to correct me.

I missed a square bracket after Breach Time within Hour... try this

TimeStamp(If(Hour([Breach Time]) > 17, [Breach Time] + MakeTime(15), [Breach Time])) as [Breach Time]

Valued Contributor

## Re: Calculation on Date

working with date or timestamp the best technique is to convert in Numeric format do you calsulations and then convert numeric back to timestamp.

Contributor II

## Re: Calculation on Date

No Sunny it was my mistake. I misunderstood.

Let me check it thoroughly.

Contributor II

## Re: Calculation on Date

Hello Sunny,

It is not working always. I am attaching my test file. Please check for INC0972080.