Calculate number of working days, hours and minutes from number of seconds

[dpoult]

Hi all,

Im creating an Cognos active report off a Data set sourced from an Excel spreadsheet. One of my columns represents a time estimate in the number of seconds. I want to convert/display the number as the number of WORKING days, hours and minutes, assuming that there are 7 hours in a working day.
I have been able to convert/display it to the number of actual days hours and minutes, but not amend the number of hours in a day.
I am using checkboxes to filter the data in the table which contains this information, i also need to be able to add a summary row which will total together this time estimate across multiple rows. Therefore i need to total the time estimate across the rows and convert/display this into the number of WORKING days, hours and minutes.

Thanks in advance for your help.
Debbie

[bdbits]

I am not sure what "amend the number of hours in a day" means, but you keep capitalizing WORKING without defining it. Are you trying to ignore weekends? Then what is the starting day?

This sounds like a straightforward but perhaps non-trivial report expression, but you need to define the elements that go into it.

[dpoult]

Hello,

For example, if i use the time interval format to display the number of days for a data item that equals 86400, it will give me 1 day, as this is 24 hours in seconds. What i want to do is tell it that instead of having 24 hours in a day, we have 7 hours in a day (as thats our working day), so one day for us is 7 x 60 x 60 = 25,200 seconds.
I have been able to achieve this by creating 5 calculations as below, and then concatenating the days, minutes and seconds but when i put this into a list query, i want it to work out the total over a number of rows, and i cant do this if ive used these calculations and concatenated the result as it doesnt make sense.

Report calculations:
Days:
FLOOR(DATAITEM/ (60 * 60 * 7))

Seconds:
FLOOR(DATAITEM- (Days * (60 * 60 * 7)))

Hours:
FLOOR(Seconds/ (60 * 60))

Seconds 2:
FLOOR(Seconds - ( Hours * (60 * 60)))

Minutes:
FLOOR(Seconds 2 / 60)

I could then use the days, minutes and hours as columns, however when it totals it in a list, it does a straight total and can come out with things like 1 day, 40 hours, 100 minutes for example.

I thought i would be able to display the dataitem in days, hours and minutes by formatting it, so the totals work correctly, however I need it to be based on the assumption there are 7 hours in a day, not 24.

Thanks in advance,
Debbie