Training frequencies - 1 year = 365 days

[mykeee]

I am working on my company's training tracking spreadsheet and I've run into a bit of a stumbling block.

I know I am probably missing something very simple here but this is what I need help with.

Some of the training we administer has a required frequency of every 1 year, 2 years, 3 years, etc. Now I have created a drop down list to select the appropriate frequency. In the cell next to it I would like to have it calculate the number of days (ex 1 year = 365 days). I need that value so I can calculate the date the employee needs to be retrained (their training date plus one year). I can't figure out how to get it to calculate this. My year list returns a text value "1 year" otherwise I would just multiply that cell by 365. Any ideas?

 

[CROY1985]

try this:

=LEFT(N2,FIND(" ",N2)-1)*365

where n2 is your cell with "1 year".

 

[Steph77]

Try =VALUE(TRIM(LEFT(D2,2)))*365

(changing D2 for wherever your text is of course)

 

[Steph77]

You might also like to use something like this:

Add dates - Excel

You wouldn't have to multiply by 365 that way and I think it would automatically adjust for leap years (but not 100% sure on that point)

 

[mykeee]

Thanks everyone! That worked great!