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.
 

makinmyway

Member
Outlook version
Outlook 2013 64 bit
Email Account
POP3
Good idea. Here we go!
 

Attachments

  • Custom Contact Form3.txt
    49.5 KB · Views: 262

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

  • Custom Contact Form6.txt
    52.5 KB · Views: 237

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

  • Untitled.txt
    53 KB · Views: 200
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
S Create a clickable custom column field Outlook VBA and Custom Forms 0
Diane Poremsky Create a custom field for Outlook messages New Slipstick.com Articles 0
Diane Poremsky Create a Custom Numbering Field for Outlook messages New Slipstick.com Articles 0
J How to create a user defined dropdown list field for a custom contact form Using Outlook 3
W How to create a cutsom task field in c#? Using Outlook 1
C Outlook VBA - Create Macro Move Contact field Item to Another Using Outlook 1
D create auto number field in task form Outlook VBA and Custom Forms 4
G Macro: Create New Message and Auto populate To Field Outlook VBA and Custom Forms 5
Chiba Create an appointment for all the members Outlook VBA and Custom Forms 1
O Create a custom contact form - questions before messing things up... Outlook VBA and Custom Forms 4
C Can't create Outlook data file Outlook Wishlist 0
L automaticaly create a teams meeting with a sync Using Outlook 0
D Can Exchange Admin Center create a pst for users email/contacts/calendar? Exchange Server Administration 0
S Create A Search Folder That Looks For Message Class? Outlook VBA and Custom Forms 0
F How to create phone number as links in notes of Contacts Using Outlook 2
Nessa Can't create new appointment Using Outlook 1
A Create date folder and move messages daily Outlook VBA and Custom Forms 1
C Create new Message with shared contacts & BCC'ing recipients Outlook VBA and Custom Forms 0
O Multiple email accounts - hesitate to create a new profile Using Outlook 3
G Can't create Folder Groups in Outlook 2013 Using Outlook 0
N Outlook rules don't create a copy for bcc'ed emails Using Outlook 3
F Delete/create/reset Exchange mailbox on Outlook.com Using Outlook.com accounts in Outlook 3
R Can not create folder to store specific emails in in Outlook for Mac Using Outlook 1
W Create Search Folder excluding Specific Email Addresses Using Outlook 5
A Outlook macro to create search folder with mail categories as criteria Outlook VBA and Custom Forms 3
K VBA BeforeItemMove event create rule to always move to its folder. Outlook VBA and Custom Forms 4
JackBlack What tools do you use to create the signature for email? Using Outlook 3
Rupert Dragwater How to create a new email with @outlook.com Using Outlook.com accounts in Outlook 32
F Should a new email account also create new contacts Using Outlook 2
D create an html table in outlook custom form 2010 using vba in MsAccess Outlook VBA and Custom Forms 7
R Outlook add-in to create new contact from an email. Using Outlook 0
Tanja Östrand Outlook 2016 - Create Macro button to add text in Subject Outlook VBA and Custom Forms 1
Q Script to create a pst file for Archiving Using Outlook 1
Jennifer Murphy Can I create a Rule with Or'd conditions? Using Outlook 1
D Outlook macros to create meeting on shared calendar Outlook VBA and Custom Forms 10
G How do I create a custom pick list in VB for an outlook automated email? Outlook VBA and Custom Forms 1
L Create Custom Form Using Outlook 0
Diane Poremsky Create a Task when a Message is Flagged New Slipstick.com Articles 0
Stilgar Relsik Create a rule to copy text from an email and paste it in the subject line. Using Outlook 1
Mark White vba to create a shared mailbox folder Outlook VBA and Custom Forms 3
Diane Poremsky Automatically create a task when sending a message New Slipstick.com Articles 0
Diane Poremsky Create a Series of Tasks using VBA New Slipstick.com Articles 0
J create .pst data file for new gmail account Using Outlook.com accounts in Outlook 1
Diane Poremsky Create an Organizational Forms Library New Slipstick.com Articles 0
Diane Poremsky Create an Organizational Forms Library in Exchange 2013 or Office 365 New Slipstick.com Articles 0
Jeanne Goodman Create Printout from Multiple Shared Calendars Outlook VBA and Custom Forms 8
Diane Poremsky Manually create a POP3 account in Outlook 2007 New Slipstick.com Articles 0
T Create Rule For Secondary E-Mail Address In Outlook 2016 Using Outlook 4
B Macro To Create Rule To Export From Certain Folder Email Information in one workbook multiple sheets Outlook VBA and Custom Forms 0
Diane Poremsky Create Appointments Using Spreadsheet Data New Slipstick.com Articles 0

Similar threads

Top