Calculate if date is < two other dates

[drehere]

Hi there - I am new to this forum so please forgive me if this has been discussed although I haven't found a solution on here yet.

I am working on an HR forecast in which I have a hire date and a term date for each employee. If the employee is currently active, their term date is input as 12/31/2016 (so they're active through the end of the year) but if they are termed at some point during the year, their term date would change to an actual date such as 5/13/2016.

When I am trying to calculate their wages per month, I want to take their hourly rate*hours in the month, (for example January has 21 working days at 8 hours each = 168 hours). I have all the months in columns BH through BS and I want the monthly amount to populate only if the hire date is less than the current month, and the term date is greater than the current month. If the hire date isn't until 4/15/16, I don't want any info showing in the Jan, Feb, Mar columns. If the term date is 10/15/16 I don't want any info showing in Nov and Dec columns.

I have a tried a couple things but I can't seem to get my formulas to work. Here is one that I have tried:

D147 = Hire date
BI145 = 2/1/2016
AX147 = rate
BH144 =hours in month
E147 = term date
BH145 = 1/1/2016

To show the monthly rate for January: =IF($D147<BI$145,$AX147*BH$144,IF($E147>BH145,0,$AX147*BH$144))

I've also tried some sumifs formulas and at this point I have just been looking at this way too long and I'm frustrated. Any help would be fantastic, thank you!

[gak67]

I would try a combination of MAX and MIN functions. Something like:

=Hourly rate * MAX(0,NETWORKDAYS(MAX(Start date,start of month),MIN(Term Date,end of month), Holidays in month)) * hours per day

[gak67]

I am assuming that if they start or end during the month they only get paid for the days they work.

To clarify my formula with your references:

Please Login or Register  to view this content.

[djbomaha]

Hi Drehere,

I have attached a workbook that I think is an example of what you are looking for.

The basic formula I used to calculate each month's pay is given below, using January 2016 as an example:

Please Login or Register  to view this content.

Range B2 = Pay Rate
Range C2 = Hire Date
Range D2 = Termination Date

The first "IF" in the line checks to see if the hire date is before the first day of the month, the next "IF" checks to see if the Termination Date is less than or equal to the last day of the month.

The example in the attached workbook will provide more explanation.

Hope it helps,

Dan

[drehere]

djbomaha, this is exactly what I was looking for, thank you!!!! I tried it and it worked; you just saved my sanity. Gak, thank you for your response as well. You two are awesome, have a wonderful night!