Number of days between the start and end dates

After reading the heading, the first formula pops up in mind is

=end date – start date + 1

but this post refer to ModelOff 2013 “Excel Golf” – 2013 Finals Group Challenge heat 1   http://www.modeloff.com/questions/

the question : Given the row start and end date, write a single formula to count the number of days between the start and end dates (inclusive) within each period.

Image

the competition required to solve it in 10 mins with shortest possible length, although I took more time than that, infact much more, but the more important thing is solution. It may not be the best or shortest but pretty simple.

the first formula i made was

=MAX(IF($F18<H$17,$F18-H$16+1,H$17-MAX($E18,H$16)+1),0)

it checks if end date is less than the period end date, if yes it subtracts the end date from period start date, else period end date is subtracted from the max of period start date or just the start date. To get rid of negative results I use the max function.

This formula was correct for the start and end date falling in different period, however incorrect for the start and end date falling in same period, so the formula lengthened to

=MAX(IF(AND($E18>=H$16,$F18<=H$17),$F18-$E18+1,IF($F18<H$17,$F18-H$16+1,H$17-MAX($E18,H$16)+1)),0)

the first IF function checks if both the dates are in same period than it gives the difference of the start and end dates

and hence the formula.

the link to question file is given for your perusal

length of my formula is 98, i m waiting for the answer file to check the shortest formula…

till then ciao

[EDIT]

Howdy people, i found a shorter formula, way shorter formula for the question

=MAX(0,MIN(H$17,$F18)-MAX(H$16,$E18)+1)

length 39

Advertisements

2 thoughts on “Number of days between the start and end dates

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s