Subtract Dates and Times field

[#1]

Hi Experts

I hope you can help :

I am loading data in from an Excel spreadsheet , I wish to be able to calculate the Turnaround time of a Task.

I have the following Columns

hh:mm            dd/mm/yyyy          hh:mm           dd/mm/yyyy    

[Start Time]     [Start Date]          [End Time]     [End date]

8:00               1/1/12                    10:00               1/1/12

8:00               1/1/12                    11:00               1/2/12

8:00               1/1/12                    11:30              1/2/12

What I'd like is to have nother column that does the calc - so see below with desired results in Hours:Minutes Format

hh:mm            dd/mm/yyyy          hh:mm           dd/mm/yyyy          hh:mm

[Start Time]     [Start Date]          [End Time]     [End date]               [turnaround]

8:00               1/1/12                    10:00               1/1/12                   2:00

8:00               1/1/12                    11:00               1/2/12                   27:00

8:00               1/1/12                    11:30              1/2/12                    27:30

Not sure if its best to do as part of the load or if we should calc in a Chart

Can anyone advise how I may do this please ?

Thanks

A

[Gysbert_Wassenaar]

It's best to calculate it in the script as part of the load. Depending on how the times and dates are stored in excel you need to determine how you can calculate the turnaround. Maybe it's possible to simply add Start Date and Start Time to get a timestamp. If you're not that lucky you may need to use the timestamp# function, e.g. something like timestamp#( [Start Date] & ' ' & [Start Time], 'DD/MM/YYYY hh:mm'). You can calculate the turnaround with the interval function. Try interval([End Date]+[End Time]-[Start Date]-[Start Time],'hh:mm') as turnaround

[#3]

Time received	Date received by RCMT	Value		Time approved / declined	Date closed	Correct Hours	Qlikview Hours	Diff
10:00:00	15/10/2012	97,295.00	15157822	08:50:00	20/10/2012	118.5	138.5	20
10:00:00	17/10/2012	1,415,779.63	Limit Ext	14:45:00	23/10/2012	148.45	168.45	20
10:00:00	18/10/2012	89,033.00	10925372	14:13:00	18/10/2012	4.13	24.13	20
10:00:00	18/10/2012	30,000.00	15855550	14:50:00	24/10/2012	148.5	168.5	20

Hi Gysbert

Thanks for the above - I was just testing it - I loaded it in the script as :

interval

([Date closed]+[Time approved / declined]-[Date received by RCMT]+[Time received],'hh:mm') as Turnaround

Then in the Table just sum(Turnaround) - It turns out the calc is incorrect by exactly 20 everytime .

Do you know why ?

Thanks
Anne

[Gysbert_Wassenaar · Accepted solution]

You add [Time received] instead of subtracting it.

You have:

interval

([Date closed]+[Time approved / declined]-[Date received by RCMT]+[Time received],'hh:mm') as Turnaround

which should be:

interval

([Date closed]+[Time approved / declined]-([Date received by RCMT]+[Time received]),'hh:mm') as Turnaround

which is the same as:

interval

([Date closed]+[Time approved / declined]-[Date received by RCMT]-[Time received],'hh:mm') as Turnaround

[#5]

Gysbert

Thanks so much -- I'll never understand how people resolve these posts !!

I really appreciate it

Anne

[#6]

Gysbert

Thanks so much -- I'll never understand how people resolve these posts !!

I really appreciate it

Anne

[#7]

Gysbert

Thanks so much -- I'll never understand how people resolve these posts !!

I really appreciate it

Anne

[#8]

Hi Gysbert

The above is how I requested - thanks a mill

Just wondering is it possible to format the same as days - see below

Thanks so much

Anne

hh:mm            dd/mm/yyyy          hh:mm           dd/mm/yyyy          hh:mm                    Days

[Start Time]     [Start Date]          [End Time]     [End date]               [turnaround]             [ turnaround]    

8:00               1/1/12                    10:00               1/1/12                   2:00                    0.08

8:00               1/1/12                    11:00               1/2/12                   27:00                1.125

8:00               1/1/12                    11:30              1/2/12                    27:30                   1.145

[#9]

Hi Gysbert

The above is how I requested - thanks a mill

Just wondering is it possible to format the same as days - see below

Thanks so much

Anne

hh:mm            dd/mm/yyyy          hh:mm           dd/mm/yyyy          hh:mm                    Days

[Start Time]     [Start Date]          [End Time]     [End date]               [turnaround]             [ turnaround]    

8:00               1/1/12                    10:00               1/1/12                   2:00                    0.08

8:00               1/1/12                    11:00               1/2/12                   27:00                1.125

8:00               1/1/12                    11:30              1/2/12                    27:30                   1.145

[Gysbert_Wassenaar]

In a chart you can change the number format on the Numbers tab of the properties screen. Set it to Fixed and specify the number of decimals you want. So you can use the same field and only change the format for presentation.