Too many IFs - combining few formulas into one - need help

[vemix]

Hi guys,

I need help in formulas... I created 6 formulas based on IFs but to be honest I think 1 is possible. In the attachment you can find the 6 formulas, but they are being based on 2 values only.

I'm trying to make a template for human resource purposes in my company. The formula just needs to calculate number of working days during the month - that's it. Number of working days is simply number of days in the month (no adjustments for holidays or Saturdays, Sundays).

The two values I mentioned is entry date of an employee and termination date. In order to make a proper calculation one must remember that there are few possibilities:
- no entry date and no termination date
- entry date and termination date
- entry date and no termination date

And upon those 3 combinations I need to calculate how many working days there are for that particular employee - so if somebody started working last month, and there is no termination date then working days will be simply 28. But if entry date 2nd of february then it will be 27, but if somebody started working on 2nd february and termination date is 27th february then it will be 25 days etc.,

I would greatly appreciate help ! I think the formulas are working, but having 6 instead of 1 is just super messy. Thanks in advance!

,vemix

[Andrew-R]

How about something like this (with start date in A1 and termination date in B1):

=DATEDIF(IF(OR(A1="",MONTH(A1)<MONTH(TODAY())),EOMONTH(TODAY(),-1)+1,A1),IF(OR(B1="",MONTH(B1)>MONTH(TODAY())),EOMONTH(TODAY(),0),B1),"d")+1

[Ron Coderre]

Using your posted workbook, I *think* this formula does what you want.
With
A1: a start date (or empty)
B1: an end date (or empty)

This regular formula returns the count of days in the A1:B1 date range that are in the current month.
• If no start date...no data is returned (empty string: "")
• If there is no end date, the last day of the current month is assumed

Please Login or Register  to view this content.

Is that something you can work with?

[vemix]

Thanks Andrew-R and Ron Coderre !

Ron, your formula works better and I will use it. Thank you so much ! Awesome ! So simple !

Thanks again guys !!!

[vemix]

Just one more thing - what kind of adjustment should I make to your formula not to show any working days if passed the termination date ? or show zero ?

[Ron Coderre]

Just one more thing - what kind of adjustment should I make to your formula not to show any working days if passed the termination date ? or show zero ?

I'm not sure I understand the question. The formula I posted already returns zero if the termination date is prior to the current month start date.
Using your posted workbook, if you change the entry date to 01-Jan-2013 and the termination date to 31-Jan-2013 the formula returns 0.

If you need something else, please post an example.