Calculate Delivery time in DD:HH:MM

[system]

Hi All,

The company I work for has implemented BO XI, and it’s wisdom given me this tool and no training. I have a pile of reports to create and so far the basics are fairly straight forward (pulling data from universes, formatting it, etc).

I work for a logistics company and one of the reports contains details of all deliveries for a given time frame (week or Month). The report is downloaded from BO into excel and the delivery time is calculated.

The calculation is Delivery date (POD_DATE) - Order date (ORDER_DATE), and the result is returned in the format DD:HH:SS.

I’d prefer to do this in BO, but so far have not been able to figure this out.

The format of both fields is: DD/MM/YYYY HH:MM.
Time is 24hr.

I’d really appreciate any pointers or assistance.

P.

---

Ranger2273 (BOB member since 2009-02-12)

[system]

Welcome to B:bob:B!

Have a look at these 2 older topics if it’s of any help to you:

Diff between two dates

I am trying to create a variable in Desktop Intelligence XI that will give me the diff between two dates. I used “=DaysBetween(<Admit Date & Time> ,<Order Date & Time>)” and I get just days. Is there a way to get his broke down to hours, minutes and seconds? djohnson (BOB member since 2007-07-26)

Convert minutes to Weeks/Days/Hours/Minutes

Hey guys, I have a column in the SQL Server database which tells me the number of minutes between 2 dates. Could anyone tell me how to convert that to the format; 3 Years, 2 Months, 1 Week, 2 Days, 13 Hours, 52 Minutes. I get lost very quickly. Cheers, Stuart stuartgmilton (BOB member since 2003-01-30)

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks for the pointers. I’ll try these and let you know how I get on.

I can see one potential pitfall and that is the format of my date fields. They are pulled from a SAP database in the form DD/MM/YYYY HH:MM (24 hr clock) but in my BO XI report they are showing as MM/DD/YYYY HH:MM (12hr clock).

I cannot find a preset data format to match original format. Is it possible to create such a format?

P

---

Ranger2273 (BOB member since 2009-02-12)

[system]

Date is a date. It contains the day part and the time part. And it’s only a matter of formatting the cell whether the hours are displayed in 12hr or 24hr clock.

You can either apply different format to the cell to display a date in a format that you need. Or you can use FormatDate() function to convert a date into a string that has a needed format.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks for the help so far. The number of seconds script worked, however the sql to display the date in the desired format does not. BOXI keeps telling me it doesn’t like the “&” character.

I am entering this as a formula for the clock of cells?

Any ideas.

Also with regards to the date format, any chance you can point me in the right direction. There only seems to be a few predefined date formats in bOXI, and no guide as how to create your own format (i.e. DD/MM/YYYY HH:MM 24hr clock)

I asked our BOXI support team and they have jsut responded it is not possible. Which I doubt.

Thanks

---

Ranger2273 (BOB member since 2009-02-12)

[system]

@Ranger2273

Also with regards to the date format, any chance you can point me in the right direction. There only seems to be a few predefined date formats in bOXI, and no guide as how to create your own format (i.e. DD/MM/YYYY HH:MM 24hr clock)

I asked our BOXI support team and they have jsut responded it is not possible. Which I doubt.

Thanks

Hi there is a difference in using hh or HH, where hh is 12 AM/PM and HH is 24 hours. MM = months and mm is minutes.

Hope this helps…

Cheers,
Robert

---

Duindam (BOB member since 2002-09-16)