# Formulating Time

#### sirrahmichelle

##### Board Regular
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 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!

Last edited:

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### westconn1

##### Board Regular
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

##### Board Regular
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.

Last edited:

#### sirrahmichelle

##### Board Regular
Does anyone have any ideas for this?

#### westconn1

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

Replies
2
Views
509
Replies
2
Views
229
Replies
0
Views
319
Replies
0
Views
647
Replies
3
Views
741

1,190,748
Messages
5,982,724
Members
439,791
Latest member
NwaTech_

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back