How to add minutes to dates

[#1]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/Add-minutes-to-date.qvw]

Hi Guys,

Attached a qvw with an example of what I need to do. Please take a look at it.

For a work order, I have to calculate when to send it to production (Target Date).

I know that "Y" is my delivery date, and "B+p" the time in minutes that I need to product the item, so TargetDate = Y - (B+p)

I also have a working days calendar, which tells me if the day is or is not a working day.

Note: I may have several work orders every day.

When merging both tables, I get the Table BaseDates, which looks like below:

Y	B+p	Formulas.SerialKey	IsWorkDay	WeekDay	WorkStart	WorkEnd
15/10/2010			1	Friday	7:00	12:00
16/10/2010			0	Saturday
17/10/2010			1	Sunday	7:00	17:00
18/10/2010			1	Monday	7:00	17:00
19/10/2010			1	Tuesday	7:00	17:00
20/10/2010	-3300.00012	telcon-7327	1	Wednesday	7:00	17:00
20/10/2010	-630.000039	telcon-7214	1	Wednesday	7:00	17:00
20/10/2010	-900	telcon-7214	1	Wednesday	7:00	17:00
21/10/2010			1	Thursday	7:00	17:00
22/10/2010			1	Friday	7:00	12:00
23/10/2010			0	Saturday
24/10/2010			1	Sunday	7:00	17:00

Now, I have to get the TargetDate for each WorkOrder as Y-(B+p).

First I am Reloading BaseDates table as JustWorkDates Table, where IsWorkDay=1.

How should I continue?

Thanks in advance,

Aldo.

[Miguel_Angel_Baeyens]

Hello Aldo,

It this waht you mean?

Date(Y - Interval(Interval#(Ceil([B+p]), 'm'), 'D'), 'DD/MM/YY hh:mm') AS TargetDate

Note that in your files the Date() function will return DD-MM-YYYY format, and your data uses DD/MM/YYYY format. You can change that in your DateFormat and TimestampFormat variables in the Main tab, so all dates are in the same format, otherwise it can be a nightmare of Date() format conversions...

[#3]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/Add-minutes-to-date4.qvw]

Hi Miguel,

Attached the qvw with some modifications.

I fixed the date formats.

The result is not what I am looking for...

In the case of

key "telcon-7214",

Y = 20/10/2010 17:00,

B+p=900.

Since both, 20/10/2010 and 19/10/2010 are Working days (600 minutes each), Target Date should be on 19/10/2010 12:00

Is there any way to solve that...?

Thanks,

Aldo.

BaseLine	Y	B+p	Key	IsWorkDay	Interv#	IntervOf Interv#	TargetDate
27	12-10-2010 17:00			1
28	13-10-2010 17:00			1
29	14-10-2010 17:00			1
30	15-10-2010 17:00			1
32	17-10-2010 17:00			1
33	18-10-2010 17:00	630	telcon-7217	1	0.4375	0.4375	18-10-2010 06:30
34	19-10-2010 17:00			1
35	20-10-2010 17:00	900	telcon-7214	1	0.625	0.625	20-10-2010 02:00
36	20-10-2010 17:00	630	telcon-7215	1	0.4375	0.4375	20-10-2010 06:30
37	20-10-2010 17:00	3300	telcon-7327	1	2.291666667	2.291666667	18-10-2010 10:00
38	21-10-2010 17:00			1