Data Validation : Including a Formula for Duration of employment

[fantismo]

Hi

Im doing a sheet to monitor employment

I need the following fields

1. DATE STARTED

2. DATE FINISHED - or - STILL EMPLOYED

3. TENURE (how many weeks employed)


so.. referring to the above..

1. Is just a date formatted blank cell.

2. I wanted a data validation dropdown, showing a STILL EMPLOYED or
further options of DATES to select from..

3. I wanted this automatically calculated in weeks and days (i.e 2
weeks, 3 days)

...............

Now if someone is
1. = 1st June... &
2. = STILL EMPLOYED.. I want...
3. to show current amount of weeks and days they have been employed
for.. from 1st june to current (does that make sense)

Ive tried a few things but i think im over complicating things..

I would really really love some help - possibly even buy a box of
chocolates

 

[Dave Peterson]

I'm not sure how Data|Validation works in this scenario...

But if you put the date started in A1.
Date finished (or still employed) in B1.

You could use this formula in C1:
=INT((IF(B1="still employed",TODAY(),B1)-A1)/7)&" weeks "
&MOD(IF(B1="still employed",TODAY(),B1)-A1,7)&" days"
(all one cell)

Personally, I'd use two different cells and keep the number of weeks separated
from the number of days--or just use the number of days.