I am currently running a spreadsheet wanting to translate dates into months of service with 4 possible scenarios. $b$2 has been set up with the date of 1/1/10 as a reference point.
The formula currently is set up as:
=if((and(B19&C19="")),12,roundup(if(b19>$b$2,b19-$b$2,if(c19>$b$2,c19-$b$2,datedif(b19,c19,"D")))/30,0))
The B column is Date of Hire and C column is Termination Date. I need the formula to give me months of service for the following scenarios:
If both columns are blank, return 12
If Date of Hire is not blank, return the difference between the start date and $b$2
If Termination Date is not blank, return the difference between $b$2 and the termination date
If both Date of Hire and Termination are not blank, return the difference between Termination Date and Date of Hire.
Right now, this formula will return the correct info in the first 3 scenarios but not the last.
Anyone able to provide some insight into this one??
Thank you!
Bookmarks