PowerPivot - Timings ignoring down hours from another table

[HarveyH123]

Hello,

I am trying to create a formulae in powerpivot that calculates time between two date/times, ignoring hours which are down hours

i.e.

If Start Time was 04/01/2018 05:53:00 and End Time was 05/01/2018 07:33:00 the answer should be 1060 minutes.

Attached is the table. Ideally i would want to do this is PowerPivot opposed to normal formula. I hope it will be simpler anyway.

To confirm, any hours in the second table that has a No shouldnt be counted in the time function.

Thanks in advance

Harvey

[/URL]

[/IMG]

 

[VBA Geek]

Hi Harvey could you post a data which can be copy pasted?

 

[HarveyH123]

Hi Harvey could you post a data which can be copy pasted?

Hi VBA Geek,

Will this OneDrive link work for you,

I don't know hoe else to upload the file

https://1drv.ms/x/s!AioeuOc0ugjWq1awVISnwcHZXZCk

thanks for your time,

Harvey

 

[VBA Geek]

Hi Harvey,

I am getting 1,113 minutes for the below:

If Start Time was 04/01/2018 05:53:00 and End Time was 05/01/2018 07:33:00 the answer should be 1060 minutes.

Can you tell me how you come up with 1,060? I am getting 1,020 minutes for January 4th and 93 minutes for January 5th.

 

[HarveyH123]

Hi Harvey,

I am getting 1,113 minutes for the below:



Can you tell me how you come up with 1,060? I am getting 1,020 minutes for January 4th and 93 minutes for January 5th.

Hi VBA Geek,

Sorry i just quickly did that number manually,

Have you made a solution?

Im sure yours will be right.

Thank you,

Harvey

 

[VBA Geek]

You can get the file here: https://1drv.ms/x/s!AiiWkkwHZChHjzZc2WmnCMahT0xz

Look at the transformations done in the Query Editor

 

[HarveyH123]

You can get the file here: https://1drv.ms/x/s!AiiWkkwHZChHjzZc2WmnCMahT0xz

Look at the transformations done in the Query Editor

Thank you, i appreciate your time.

Im on holiday until Wednesday,

Ill look then and get back to you

Thanks again

 

[HarveyH123]

You can get the file here: https://1drv.ms/x/s!AiiWkkwHZChHjzZc2WmnCMahT0xz

Look at the transformations done in the Query Editor

This is a great way to do it VBA Geek,

I changed it slightly and made it so it only uses Power Query opposed to PowerPivot as well.

As seen here, i also made it so we can see the proportion of valid and non-valid time.

https://1drv.ms/x/s!AioeuOc0ugjWq1hsib6LDXHDtC7L

As seen here.

List.DateTimes( [Start Time]) is going to be a great function when working in times in the future.

Cheers for your help,