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
 
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
 
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!
 
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?
 
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),"")
 
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"
 
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.
 
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?
 
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.
 
Good idea. Here we go!
 

Attachments

  • Custom Contact Form3.txt
    49.5 KB · Views: 456
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
 
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: 434
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: 391
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
J How to create a drop down user defined field that will appear on an inbox view Outlook VBA and Custom Forms 8
S Create a clickable custom column field Outlook VBA and Custom Forms 0
Diane Poremsky Create a custom field for Outlook messages Using Outlook 0
Diane Poremsky Create a Custom Numbering Field for Outlook messages Using Outlook 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
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
S Create Outlook Task from Template and append Body with Email Body Outlook VBA and Custom Forms 4
B Modify VBA to create a RULE to block multiple messages Outlook VBA and Custom Forms 0
J Want to create a button on the nav bar (module add-in) to run code Outlook VBA and Custom Forms 2
B How to create a button that sorts and selects the most recent message with ONE click Using Outlook 2
J PSA: How to create custom keyboard shortcut for "Paste Unformatted Text" in Outlook on Windows Outlook VBA and Custom Forms 1
W Create a Quick Step or VBA to SAVE AS PDF in G:|Data|Client File Outlook VBA and Custom Forms 1
Wotme create email only data file Using Outlook 1
S Outlook 365 Help me create a Macro to make some received emails into tasks? Outlook VBA and Custom Forms 1
Commodore Any way to create "from-only" account on Outlook 2021? Using Outlook 1
L Capture email addresses and create a comma separated list Outlook VBA and Custom Forms 5
N Can't create NEW GROUP and add/remove a member from existing Group in Outlook Using Outlook 1
NVDon Create new Move To Folder list Outlook VBA and Custom Forms 0
C Create Meeting With Custom Form Outlook VBA and Custom Forms 2
D Create advanced search (email) via VBA with LONG QUERY (>1024 char) Outlook VBA and Custom Forms 2
G Create ordinal numbers for birthday Outlook VBA and Custom Forms 2
O Outlook 365 - How to create / copy a new contact from an existing one? Using Outlook 5
D Create new email from the received Email Body with attachment Outlook VBA and Custom Forms 10
A How to create fixed signatures for aliases that process through GMAIL? Outlook VBA and Custom Forms 0
P Can I create a Rule that sends me an email when I get a Task? Using Outlook 2
M How create a Rule to filter sender's email with more that one @ sign Using Outlook 1
B Can I create a local PST file for SPAM on a drive that is usually disconnected? Using Outlook 3
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
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

Similar threads

Back
Top