Formulating Time

[sirrahmichelle]

I can't have the nifty addons that do the fancy tables at work, so please forgive me.

For some background, I have a spreadsheet that appears like this:


Column F= "Years/Mos EXP"
Column G= "Adjusted EXP"
Column O= "Last Updated"
Column Q= "Days EXP"

Formula in F3: =(YEAR(Table1[[#This Row],[Column2]])-1900&" years, "&MONTH(Table1[[#This Row],[Column2]])&" months")
Formula in G3: =YEARS(Table1[[#This Row],[Yrs/Mos Exp]]-Table1[[#This Row],[Last Date Qual''d]])
Contents in O3: 10/18/2005
Contents in Q3: 5035

Displayed in F3: 13 years, 10 months
Displayed in G3: #NAME?
Displayed in O3: 10/18/05
Displayed in Q3: 5035

What I am trying to do is simplify entering years and months into a cell to populate F3. I have somewhat figured out how to do this by breaking the time out into days and inserting the day amount into Q3 and doing the calculation shown above in F3. Is there an easier way to do this?

Moving right along, I need G3 to add the time lapsed from =today() since the date provided in O3(Last updated) and then add it to the time shown in F3. So if someone has 10 years exp(F3), and a year has past since the last update(O3), Adjusted EXP(G3) displays 11 years exp. Does anyone know how to do this?

I hope this makes sense. Let me know, thanks!

 

[westconn1]

i would believe you should have a column for commencement date or experience start date, which would never chnage, then easy to calculate all up to date,

 

[sirrahmichelle]

That doesn't really apply to my situation. When people work through out their lives, they sometimes take breaks for school, unemployment, or short lived retirements. This is why i start with a block of time, and then formulate it into years and months.

 

[sirrahmichelle]

Does anyone have any ideas for this?

 

[westconn1]

don't know if this helps
="years "&INT(DAYS360(O3,TODAY())/365)&" months "& INT((DAYS360(O3,TODAY())-INT(DAYS360(O3,TODAY())/365)*365)/30)