Stumped on a formula involving dates

ORDub

New Member
Joined
Apr 10, 2013
Messages
16
Platform
  1. MacOS
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.

If the above does not help, we may need more information.

N.B. The dates are Jan 1 2013 and Feb 1 2013
 
Upvote 0
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.
 
Upvote 0
Feb 13
Start DateTerm Date
1/1/133/1/13XXX
1/1/13YYY

<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).
 
Upvote 0
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:
Upvote 0
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. :(
 
Upvote 0
Did you try the suggestion in post # 2 that was based on your information?
 
Upvote 0
Yes....and if the term date field is blank, the formula zeroes out the calculation.
 
Upvote 0
"Yes....and if the term date field is blank, the formula zeroes out the calculation. "

That is not what the formula does.
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top