BusinessObjects Board

Import hours from Excel

building reports desktopintelligence
#1

My MS-Excel sheet contains two columns (A and B) with hours.
The format of these columns in Excel is: [u]:mm. The Excelformulabar shows ‘8-1-1900 17:00:00’ and the ‘translation’ to the particular cell is: 406:00

Now I would like to import this sheet in BusObj (6.5) with the right dataset,
what enables me to make a calculation between hourcolumn A - hourcolumn B

For example:

column A contains : 406:00 column B contains: 399:00 calculated field C shows 007:00

Pepper (BOB member since 2004-09-17)

#2

Hi,
Do you want to import the sheet to Business objects and then do the calculation.

harpreet_lucky (BOB member since 2006-04-24)

#3

All the calculations have to be done by BO, even this one.

Pepper (BOB member since 2004-09-17)

#4

In this case you need to creat a variable and then there is a function DaysBetween(,) you need to apply this then.
Hope it works.

harpreet_lucky (BOB member since 2006-04-24)

#5

Thanks for your fast reply, but… it doesn’t work.

I tried: DaysBetween(dateA,dateB) the result is in some cases an non-existing date 28/11/60238

Pepper (BOB member since 2004-09-17)

#6

Hey I know the problem you are facing actually you now need to format the cell and make it 0.This will definelty work.
Right Click on that column ,click on Number Tab and then All (on the left side) and choose o.
keep me posted.

harpreet_lucky (BOB member since 2006-04-24)

#7

Probally due to my intellectual capacities I haven’t found the solution.

Enclosed you will see the Excel-file with those two columns, which have to be imported in the same way as they are defined in Excel.

Maybe it helps to express my problem I have in a better way.
tmptst2.xls (152.0 KB)

Pepper (BOB member since 2004-09-17)

#8

Hi,

Using BO 5.1.6 it will force a date format on both time objects when imported in BO. Does that happen in BO 6.5 aswell?

If so, you might want to add two columns in your excel sheet without the [u]:mm format or an extra sheet which is linked to the orignal. In this second tab discard the time format, which will leave you with the numerical value of both objects. This you can consequently import in BO to calculate the time value of the objects and the difference between the two.

You can also try the macro in the FAQ submitted by Anita and Dave which will enable you to override the format BO uses to import data from excel.

Bazzer :netherlands: (BOB member since 2005-10-18)

#9

After some days I found a way how to calculate with the Excel-hours

Enclosed you will find the sample Excel-file.

After importing this file into BusObj you can make your own computation with the hours you need. You can’t make use of the original Excel-hournotation.
BOBhour.xls (130.0 KB)

Pepper (BOB member since 2004-09-17)