How Create a Field and Formula for a Contact's Years of Experience

Status
Not open for further replies.

makinmyway

Member
Outlook version
Outlook 2013 64 bit
Email Account
POP3
I'd like to know if there is a way to easily make Outlook show me the years of experience at Contact has any given field. I was imagining using one of the user fields along with a formula that keeps adding a year to the total years of experience a person has based on the number I enter into that field. For instance, if someone started work in 2014, I would enter that information into the user fields and then I want to know how to create a search or a view that would show me the number of years the person has worked which at this point would be two years. Alternately as a recruiter, I want to be able to run searches through my contacts for individuals that meet criteria for a job. One of the most common criteria is a minimum number of years of experience. How can I begin to capture that information and create advanced searches that incorporate this information, along with Categories and keywords
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
You can, assuming you have a field on the contact that holds the start date. Just create a second field as a formula field and use now - start date as the formula. This sample uses the birthday field but the basic idea is the same. - To calculate the age of an Outlook contact
 

makinmyway

Member
Outlook version
Outlook 2013 64 bit
Email Account
POP3
Yes that is helpful, Diane. Just what I needed. Now I just need to add and populuate a field when I create a new contact with the year they started work in the industry. Thank you!
 

makinmyway

Member
Outlook version
Outlook 2013 64 bit
Email Account
POP3
Is there a way to enter a year (and not a day and month)? Right now I have choosen the field format for a start date field of 2/2016 from the drop down choices in the field designer. But when I try to enter anything else (say 09/2014) it populates the field with 2/2014). I'd like to have a formula/format that gives me the number of years and (if possible) months of experience from the start date field. Am I choosing the wrong field or format?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
What formula are you using? Getting months and years shouldn't be a problem.

Using the birthday example, DateDiff calculates days - you could either divide the result by 30 to get months or just work in months - I think you just need to change the d to m, but haven't tested it to verify. Then divide the results by 12 to get years.

DateDiff("yyyy",[Birthday],Date())-IIf(DateDiff("d", CDate(Month([Birthday]) & "/" & Day([Birthday]) & "/" & Year(Date())),Date())<0,1,0),"")
 

makinmyway

Member
Outlook version
Outlook 2013 64 bit
Email Account
POP3
Diane, I am using "(Now() - [Anniversary] )/365" where the Anniversary field is in the form of 2/2015 (an option in the drop down choices when designing the field's value). But every time I publish and use the form it changes any month back to 2 upon leaving the field. The year I enter is always left correct and so is the calculation field in that it gives me the correct number of years of experience in the industry with the formula. But why does it not respect what I put into the field when trying to use it when it comes to the month portion??? Note in the Design A Form under the field into which I want to enter a month and year, I move to Properties, then Value and then Format and select from the choices "2/2016". It's the format I want but I don't want it to always be the 2nd month of the year. Is this just a sample showing the format or does this part of the Design a Form have something to do with my problem? Also, how would I eliminate the many digits after the year in the "(Now() - [Anniversary]/365" formula above?
"(Now() - [Anniversary] )/365"
"(Now() - [Anniversary] )/365"
"(Now() - [Anniversary] )/365"
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Int ( (Now()-[Anniversary])/365) will give you whole numbers - if you want 2 decimal points, use the datediff formula on the calculate age of contact page.

I'm not sure why outlook is not showing the correct date when you use 2/2016 format - if you post the template, i'll take a look at it.
 

makinmyway

Member
Outlook version
Outlook 2013 64 bit
Email Account
POP3
The uploaded file does not have the correct extension is the message I get when I try to upload the .oft form even as a .rar file. What format will this forum accept instead?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
I thought it took any common file type.... I know it takes images and (i think) zip and text. Try adding .txt to the extension and see if it works.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
That worked... and the form is working here. I thought maybe it was the option in the field to calculate it when composing a new form - but i used it both with the automatic setting and new form setting and both are keeping the month. It really shouldn't matter though since it's just a copy of the anniversary field.

These are the fields from both on my samples:

form2.png


Now the default is 2/2015 - because there was a value in the anniversary field when published. Delete the date from the anniversary and birthday field and publish.


form.png
 

makinmyway

Member
Outlook version
Outlook 2013 64 bit
Email Account
POP3
I worked with the form you sent back but I still think it is buggy as I could not get the Birthday formula to work for me and even deleting the date from the form before publishing still populates the fields with the 3rd month of the year regardless of the month and year I enter into either of the two fields I am trying to log Date Started Industry and Date of Last Contact. I think the formula was right in the calculation for the Time In the Industry field but then I fiddled with it thinking I could get a year with one decimal to the right and saved over your original formula and lost it. I never did get the Birthday formula to calculate the correct length of time since we last connected. Can you tell/show me where I went wrong by looking at my latest form which I have attached? I felt like I was so close, Carol, and then I mucked it up trying to twee
 

Attachments

makinmyway

Member
Outlook version
Outlook 2013 64 bit
Email Account
POP3
Does anyone have any thoughts about the two formulas in the latest version of my Custom Contact Form (see attached)? I am failing to get it to work as it pertains to the four fields in the upper right corner, one of which determines the length of time the contact has worked from an anniversary date field (by years with one decimal point, i.e., "12.5" years of industry experience) and the other that shows the length of time since our last contact (by years with one decimal point as well) again based on a date entered by hand. Each time I work with the above formulas mentioned in the thread I get really odd answers like "12/1899" or the same number I entered in the Anniversary or Birthday field repeated in the formuala's outpoint field. Any further insight would be much appreciated. Michael
 

Attachments

Status
Not open for further replies.
Top