I have an Excel sheet that contains employee information and there is a column for the date of birth, and I want to calculate their age, and I also want to convert their date of birth into this formula, for example:
March 12, 1994
-
For calculating date of Birth, you can subtract the brithdate and current date and convert that difference into year to get age
-
Regarding your question on date format of birth date, I would suggest try either of 2 options
a. Read excel , and for each row for Birthday column, change the date format and then use workbook write range to write the updated data
OR
b. use a vba script to change the format of Birthday column to specific date format & use invoke vba code to invoke vba in the excel for converting date format.
For VBA refer the below link:
Thanks & Regards,
Nived N
- To convert date on excel…use format cell activity and try changing the format as required…
- For the age you can directly caculate using excel formula…for that you need to write the below formula in the first cell which is L2 using a write cell activity and then use auto fill formula activity
"=TRUNC(YEARFRAC(K2,TODAY()))"
Hope this helps
Cheers
Hi @Muneer_Alrashdan ,
Please follow the below steps to achieve your requirement,
- Inside excel application scope, Use ‘read range’ activity and store the output in a datatable var.
- Inside ‘for each row in datatable’ activity, use ‘get row item’ activity to read Birthday column data one-by-one.
- Use below code calculate their age in year,
int_years = DateDiff(DateInterval.Year, DateTime.ParseExact(Strinput.ToString, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture),DateTime.now())
- Now use the below code to change the date formatting and then you can use update row item to update the same in the datatable.
var_datetime = Datetime.ParseExact(Strinput.ToString, “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture).toString("MMM dd, yyyy")
Hope this works for you.
Regards,
Ashutosh Gupta