Calculate days out on sick leave by month

PIJim

Board Regular
Joined
Jul 29, 2005
Messages
143
I need to calculate how many days an individual is out on sick leave (including weekend, scheduled days off and holidays).

I need to do this throughout the year and there are many rows of information.

A2 = 1/30/06(1st day out)
B2 = 3/15/06(last day out)
C1 = January // C2 = 2(result)
D1 = February // D2 = 28(result)
E1 = March // E2 = 15(result)

I need a formula to caluculate C2, D2 & E2

Any help???
Thanks
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try this formula in C2 copied across

=SUMPRODUCT(--(TEXT(ROW(INDIRECT($A2&":"&$B2)),"mmmm")=C$1))
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Barry, wouldn't mind an explanation for that one as I have been trying to work that one out unsuccessfully.

This is as far as I got but it doesn't work

=MIN($B$2,EOMONTH(C1,0))-MAX($A$2,EOMONTH(C1,0)) ( this needs adate in row 1)

Interested in how yours works
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Hi GorD,

If there are dates in C1 etc then you can use

=MAX(0,MIN($B$2,EOMONTH(C1,0))+1-MAX($A$2,EOMONTH(C1,-1)+1))

Clearly my solution above assumes Text in C1

ROW(INDIRECT($A2&":"&$B2)

returns an array of date serial numbers corresponding to all dates between A2 and B2. The TEXT function converts these to "January", "February" etc and SUMPRODUCT simply counts those that are equal to the text in C1, thereby giving a count of the dates in the relevant month.

Of course it takes no account of the year.....
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
543
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You'll want to upgrade to XL07, as well - otherwise Barry's formula will crash on dates after June, 2079!
:LOL:
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You'll want to upgrade to XL07, as well - otherwise Barry's formula will crash on dates after June, 2079!
:LOL:

Hi Dean,

That's true - that's one disadvantage of that type of formula - but I'd hazard a guess that PIJim isn't too concerned about dates 70 years in the future.....

Possibly wiser, though, to use the option suggested by GorD with dates in row 1 formatted as mmmm or even mmm yyyy.

Of course it'll cope with dates up to 2083 if you're using 1904 date system :cool:

edit: thanks for the PM Dean, made me realise I could amend my original suggestion to

=SUMPRODUCT(--(TEXT($A3+ROW(INDIRECT("1:"&$B3-$A3+1))-1,"mmmm")=C$1))

which works for any dates as long as the period between them is less than 179 years (approx) :cool:
 

PIJim

Board Regular
Joined
Jul 29, 2005
Messages
143
barry houdini's formula in his first post works perfectly!

THANKS!
 

Forum statistics

Threads
1,136,267
Messages
5,674,730
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top