Hi,
I have two problems.

1)I'm using datediff function to count difference in minutes between
two dates - and it's working fine.
But I would like to ask if there is any function (or maybe U could tell
me how to do it) that is able to count difference between two dates in
minutes, including only days from Monday to Friday and hours from 9:00
am to 5:15 pm.

2)My second question reference to the first one. Let's suppose that
I've managed to count correctly difference between two dates. But for
example: 120332 minutes is not telling me a lot. So I would like to
show this measure in this format DD:HH:MM
(DaysDays:HoursHours:MinutesMinutes) - aggregation function for this
measure is SUM.
I was trying to use MDX language to change 120332 into DD:HH:MM and
show this result in report but it didn't work - I don't know how
to do it correctly. Or maybe U have a better idea how I should do it.

Can You help me?

For the 1st problem, if the date range isn't too large and you're using
AS 2005, you could create a combined date/time dimension down to the
minute granularity (1440 members/day). Each minute member could have an
"InWorkHours" attribute, based on your conditions: including only days
from Monday to Friday and hours from 9:00 am to 5:15 pm.

Then, the difference in minutes between 2 minute members could be
computed by subtracting their Rank values in a set consisting only of
"InWorkHours" minutes, like:

Rank([DateTime].[DateTime].CurrentMember,
Exists([DateTime].[DateTime].[Minute].Members,
{[DateTime].[InWorkHours].[Yes]}))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

For the elapsed period Measure in time format, I issued the needs as a beta
feedback, but it did not be reflected.

Anyway, in AS 2000 next expression can be used.

WITH
MEMBER Measures.hhmmss AS '[Elapsed Seconds] * 1/60/60/24',FORMAT_STRING =
'hh:mm:ss'
MEMBER Measures.days AS 'DateDiff("d", CDate(0),CDate(hhmmss))'
MEMBER Measures.Elapsed AS 'CStr(days) + " Days " +
Format(hhmmss,"hh:mm:ss")'

It's easy to reformat to meet your format.

But looks like that VBA Format function does not work in AS 2005. I don't
know why.
Of course, it's possible to get the formatted value through tiresome coding
as following,

MEMBER Measures.ET AS [Elapsed Seconds]
MEMBER Measures.Elapsed2 AS
CStr(Int(ET/3600/24))+ " Days "
+ right("0" + CStr(Int((ET - Int(ET/3600/24)*3600*24)/3600)), 2)+ ":"
+ right("0" + CStr(Int(((ET - Int(ET/3600/24)*3600*24)/3600-Int((ET -
Int(ET/3600/24)*3600*24)/3600))*60)), 2)+ ":"
+ right("0" + CStr(Int((((ET - Int(ET/3600/24)*3600*24)/3600-Int((ET -
Int(ET/3600/24)*3600*24)/3600))*60 - Int(((ET -
Int(ET/3600/24)*3600*24)/3600-Int((ET -
Int(ET/3600/24)*3600*24)/3600))*60))*60)),2)

Ohjoo

"cahirek" <michalgolawski (AT) gmail (DOT) com> wrote

Thanks Ohjoo!!
Based on Yours function I wrote something like that
(DaysDays:HoursHours:MinutesMinutes):

MEMBER Measures.ET AS [Elapsed Minutes]
MEMBER Measures.Elapsed2 AS
right("0" + CStr(Int((Int(ET/60))/24)), 2)+ ":"
+ right("0" + CStr(Int(ET/60)-(Int((Int(ET/60))/24))*24), 2)+ ":"
+ right("0" + CStr(ET - (Int((Int(ET/60))/24)*24 +
Int(Int(ET/60)-(Int((Int(ET/60))/24))*24))*60 ),2)