Formula to calculate how many days in a month have Daylight Savings

[kersplash]

In the attached, the Business Hours column calculates the number of staffed hours per business days in the month.

The problem I have is when daylight savings is in effect, there are 12 hours per business, and 11 hours per business day when daylight savings is not in effect.

I need a formula to take into account the change of daylight savings in the Business Hours field.

[Bo_Ry]

Please try at D2

=[@[Month Business Days]]*12-((MONTH(A2)>=MONTH($O$2))+(MONTH(A2)<=MONTH($P$2)))*NETWORKDAYS(MAX(EOMONTH(A2,-1)+1,$O$2),MIN(EOMONTH(A2,0),$P$2),tbl_PH[P/H])

[kersplash]

Thanks for the formula Bo_Ry.

I would like this to include future daylight savings times as well which will be listed in the Daylight Savings Table.

[Bo_Ry]

kersplash,

Is the daylight savings will be between Oct - Apr every year?

[kersplash]

Also your formula results in the same number of hours as 12 x Month Business Days for July, but July is outside of Daylight Savings so should be 11 x Month Business Days.

[kersplash]

kersplash,

Is the daylight savings will be between Oct - Apr every year?

Daylight Saving Time begins at 2am on the first Sunday in October, when clocks are put forward one hour. It ends at 2am (which is 3am Daylight Saving Time) on the first Sunday in April, when clocks are put back one hour.

[Bo_Ry]

Please try at F2

Please Login or Register  to view this content.

O2
=WORKDAY.INTL(--("oct"&LEFT(N2,4)),1,"1111110")

E2
=WORKDAY.INTL(--("apr"&LEFT(N2,4)+1),1,"1111110")

[Bo_Ry]

Also your formula results in the same number of hours as 12 x Month Business Days for July, but July is outside of Daylight Savings so should be 11 x Month Business Days.

Oh, I invert for daylight count for 11 hr.

[Bo_Ry]

Please try again at F2

=[@[Month Business Days]]*11+IFERROR(((MONTH(A2)>=MONTH($P$2))+(MONTH(A2)<=MONTH($Q$2)))*NETWORKDAYS(MAX(EOMONTH(A2,-1)+1,LOOKUP(EOMONTH(A2,0),tbl_DS[Start])),MIN(EOMONTH(A2,0),LOOKUP(EOMONTH(A2,0),tbl_DS[Start],tbl_DS[End])),tbl_PH[P/H]),)

[kersplash]

Thanks Bo_Ry. You got me what I needed. Just had to modify your final result slightly. Attached is the final.

Thankyou.

[kersplash]

Oh, I invert for daylight count for 11 hr.

That's what I thought. All fixed. Thanks.

[kersplash]

Hi Bo_Ry.

Sorry, one more thing the Start Date for 2023 should be 1st October but your formula gives 8th October.

[Bo_Ry]

Please try

=WORKDAY.INTL(--("oct"&LEFT(O2,4))-1,1,"1111110")

or

=--("oct"&LEFT(O2,4))-WEEKDAY(("oct"&LEFT(O2,4)),11)+7

[kersplash]

Please try

=WORKDAY.INTL(--("oct"&LEFT(O2,4))-1,1,"1111110")

or

=--("oct"&LEFT(O2,4))-WEEKDAY(("oct"&LEFT(O2,4)),11)+7

That works.

However still when 2017/18 is the year the End date should be 1/4/2018 not 8/4/2018.

[Bo_Ry]

Same as Oct

=WORKDAY.INTL(--("apr"&LEFT(P2,4)+1)-1,1,"1111110")

[kersplash]

Perfect. Thanks again.

[kersplash]

Another problem, the formula now for calculating the number of Daylight Savings Days is broken with those changes. See attached.

[Bo_Ry]

You forgot +1 at Data sheet G2

=WORKDAY.INTL(--("apr"&LEFT([@[Daylight Savings]],4)+1)-1,1,"1111110")

[kersplash]

You forgot +1 at Data sheet G2

=WORKDAY.INTL(--("apr"&LEFT([@[Daylight Savings]],4)+1)-1,1,"1111110")

Hi Bo-Ry,

Thanks but this gives the incorrect dates. The start and end dates are correct and I've worked on a different method in the attached which seems to be working correctly.

Thanks