Calculate the number of winter days between two dates

Hello everybody,

I'm looking for the formula to calculate the number of winter days between two dates.

Say, that I'm having:
- Winter season start date (say Dec 1st)
- Winter season end date (say, March 1st)

=> All the days which are between these two are considered winter days for any given year.

- Two different dates defining the beginning and the end of my PERIOD. The total length of the period may be more than one year.

HOW do I calculate total number of winter days for the PERIOD?

Thanks a lot!!

Hi,

Is there any significance to the word 'winter'. Doesn't this question boil down to the number of days between two dates?

If so with the earlier date in A1 and last date in A2

=a2-a1

plus 1 if you want days inclusive or minus 1 if you want exclusive dates.

Late edit. Ok I see the proviso about more than one year. Ignore the above.

Try this:

Data Range

A

B

C

D

E

F

G

H

I

J

K

L

1

Start Winter

End Winter

Start Date

End Date

2

12/1/2013

3/1/2014

1/15/2014

2/25/2014

41

3

4

5

Formula in E2

6

'=IF(AND(C2<A2,D2>B2),B2-A2,IF(AND(C2<A2,D2<B2),D2-A2,IF(AND(C2>A2,D2>B2),B2-C2,IF(AND(C2>A2,D2<B2),D2-C2,""))))

7

Since your date range may include multiple years...
A1: start date....2013-01-01
A2: end date....2014-06-30

C1: winter start...2013-01-01
D1: winter end....2014-03-01

This regular formula returns the count of dates that fall in the Winter period(s)

Please Login or Register  to view this content.

In the above example, the formula returns: 151

Is that something you can work with?

The formula I posted works for multiple years.
The reason that I used actual dates for the start and end of winter, instead of 1201 and 0301, is this:
I would still need to convert 1201 and 0301 to something the formula can use for test purposes...This would be that formula, which (IMHO) causes confusion about subtracting 1100 and adding 100 to align dates correctly(but, it's up to you):
C1: (formated as text) 1201
D1: (formated as text) 0301

Formula:

Please Login or Register  to view this content.

Does that help?

Here's another approach.

With -
Period First Date in B1 and End Date in B2
Typical Winter Start Date in E1
Typical Winter End Date in E2

The number of winter days between your two dates in A1 & A2

Formula:

Please Login or Register  to view this content.

I'm also attaching a workbook which shows the generalised approach

When I run that formula for 2013-01-01 through 2015-06-30, it returns 180...I was expecting 242.
Am I missing something?

Originally Posted by Ron Coderre

When I run that formula for 2013-01-01 through 2015-06-30, it returns 180...I was expecting 242.
Am I missing something?

No Ron, I think it's me!

Belay that last post. Let me check it.

Ron's formula works fine for me, winter period doesn't need to be start of the year

Originally Posted by evp2

Another difficulty, is that I also needed to count only WORKING days.

What constitutes a working day, do you want to exclude just Saturdays and Sundays or holidays too? If you aren't worried about excluding actual holidays then you can use the holiday field in NETWORKDAYS to exclude summer days, e.g. this array formula

=NETWORKDAYS(A2,B2,IF((TEXT(ROW(INDIRECT(A2&":"&B2)),"mdd")+0>301)*(TEXT(ROW(INDIRECT(A2&":"&B2)),"mdd")+0<1201),ROW(INDIRECT(A2&":"&B2)),0))

confirmed with CTRL+SHIFT+ENTER

A2 = start date, B2 = end date and 1201 and 301 in the formula represent the start and end dates of winter, change as required

@trilla99

It is not allowed to start your own question in another one's question.

Start your own question for this an refer to this topic if you think it is helpfull.

Please also add an small excel example, without confidential information.

It is also helpfull if you add the desired result in your file.