# Stumped on a formula involving dates

#### ORDub

##### New Member
So I am creating a table that would allow for users to input an employee start date and a termination date. In most cases, the termination date will be left blank. I need a formula to calculate the months labor expense if the current month is >= the start month, and also <= termination date/month. I created a simple If(And for it....but when the Termination date is blank, my formula zeros out (assumes the employee is gone). How do I fix this?

Same:

Cell A5 is the Start Date (lets say its populated with 1/1/13). Cell B5 is the Termination date (assume it is blank). And assume cell C4 is the current month (lets say 2/1/13).

Thank you!

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Excel Workbook
ABCD
1
2Monthly cost\$10,000.00
3
401/02/13
501/01/13\$10,000.00
3b
Excel 2003
Cell Formulas
RangeFormula
C5=IF(B5>0,AND(C4>A5,C4),(C4>A5))*C2

Welcome to the forum.

N.B. The dates are Jan 1 2013 and Feb 1 2013

Not sure how to do a table on here.... so using the one you provided.... Assuming A5 would be the start date, and C4 would be the termination date... the formula needs to be such that if the current month (assume that D1 is a month) is greater than or equal to A5 AND that it is equal to or less than C4, it would give me the monthly cost. That formula is easy enough. What's hosing me is if C4 is blank....meaning the employee isnt terminated at all.

 Feb 13 Start Date Term Date 1/1/13 3/1/13 XXX 1/1/13 YYY

<tbody>
</tbody>

Better way to show it (yes, finally figured out the Advanced feature). The formula needs to work for both XXX and YYY (difference being that in the second example row, there is no Term Date (its blank).

If there is no termination date, then the current date would be the termination date(last available date), right? Why not throw and IF statement saying if termination date is "", to use the current date?

Last edited:
It wouldnt be zero....it would be a blank cell. I sort of have a work-around in place right now...but its a bigger formula that I'd like, and I have a LOT of them...so file size just bloated on me.

Sorry. Meant "", not 0.

Did you try the suggestion in post # 2 that was based on your information?

Yes....and if the term date field is blank, the formula zeroes out the calculation.

"Yes....and if the term date field is blank, the formula zeroes out the calculation. "

That is not what the formula does.

Replies
1
Views
307
Replies
4
Views
331
Replies
23
Views
539
Replies
4
Views
198
Replies
7
Views
243

1,203,101
Messages
6,053,530
Members
444,670
Latest member
laurenmjones1111

### 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.

### Which adblocker are you using?

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

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