Help with a formula using dates and empty cells

Hi Folks,

Firstly, thanks in advance for anyone taking the time to help with this. I know we're all very busy!

Secondly, apologies if this is too long, I'm just trying to be as detailed as possible.


Right, I have a spreadsheet with all my members of staff on, names, addresses, pay rates, dates of employment, length of service etc. My problem is with the date of employment/length of service columns. What I currently do is have one table for current staff members, and one for ex-members of staff. So the "Current Staff" sheet doesn't have an "End Date" column, and when someone leaves I'll cutting and pasting the row into the the "Ex-Staff" sheet, which has an "End Date" column and that does the job. Which I guess is fine. But it would be really useful to have everything on one sheet for several reasons and I need a bit of help.

I need the "Length of Service" column (a. it's useful & b. it affects holiday entitlement) and I've found a way of displaying it in Years, Months & Days (which is in place on the "Ex-Staff" sheet).

The sheet I'm trying to do would have both current and ex staff on this new sheet. What I'd really like is to have a "Start Date" column, a "Leaving Date" column & a "Length of Service" column. The formula I'm having trouble with is for the "Service" column because I'd like it to do two things...

1) If there is no data in the "Leaving Date" column I'd like the "Service" to work out [today's date-Start date]
2) If there IS data in the "Leaving Date" column I'd like the "Service" column to work out [Leaving Date-Start Date]

I know how to do these relatively simple things separately (hence me using two different sheets at the minute) and I'm assuming it's possible to do it in one cell? But I'm struggling!



P.s. I'd also really like it if the whole row could turn red if there was a date in the "Leaving Date" column. Again, I'm sure I could do this pretty easily if it were a stand alone requirement but don't know how to get it to do all the things at once!

Many Thanks,

Chris