Employee Service Length Calculator<\/title>\n <script src=\"https:\/\/cdnjs.cloudflare.com\/ajax\/libs\/xlsx\/0.18.5\/xlsx.full.min.js\"><\/script>\n <script src=\"https:\/\/cdnjs.cloudflare.com\/ajax\/libs\/FileSaver.js\/2.0.5\/FileSaver.min.js\"><\/script>\n <style>\n body {\n font-family: Arial, sans-serif;\n padding: 20px;\n margin: 0;\n }\n\n h1 {\n text-align: center;\n }\n\n table {\n width: 100%;\n margin-top: 20px;\n border-collapse: collapse;\n }\n\n table, th, td {\n border: 1px solid #ddd;\n text-align: left;\n }\n\n th, td {\n padding: 10px;\n }\n\n button, input[type=\"file\"], input[type=\"date\"] {\n margin: 10px 0;\n padding: 10px;\n font-size: 16px;\n }\n\n label {\n display: block;\n margin: 10px 0;\n }\n <\/style>\n<\/head>\n<body>\n <h1>Employee Service Length Calculator With Excel Data Import & Export<\/h1>\n\n <label for='fileInput'>Please choose an Excel file where the first coloumn contains 'Employee ID' and the second coloumn contains 'Date of Joining'. Do not add any other coloumn. After exporting the calculation to Excel file you may use XLOOKUP or VLOOKUP to match with large data if required.<\/label>\n <input type=\"file\" id=\"fileInput\" accept=\".xlsx, .xls\">\n <br>\n\n <label for=\"calcDate\">Calculate service length as of:<\/label>\n <input type=\"date\" id=\"calcDate\">\n <br>\n\n <button id=\"calculateButton\">Calculate Service Length<\/button>\n <button id=\"exportButton\" disabled>Export to Excel<\/button>\n\n <table id=\"dataTable\">\n <thead>\n <tr id=\"tableHeader\"><\/tr>\n <\/thead>\n <tbody id=\"tableBody\"><\/tbody>\n <\/table>\n\n <script>\n const fileInput = document.getElementById('fileInput');\n const calcDateInput = document.getElementById('calcDate');\n const calculateButton = document.getElementById('calculateButton');\n const exportButton = document.getElementById('exportButton');\n const tableHeader = document.getElementById('tableHeader');\n const tableBody = document.getElementById('tableBody');\n let employeeData = [];\n\n fileInput.addEventListener('change', handleFile);\n calculateButton.addEventListener('click', calculateServiceLength);\n exportButton.addEventListener('click', exportToExcel);\n\n function handleFile(event) {\n const file = event.target.files[0];\n if (!file) return;\n\n const reader = new FileReader();\n reader.onload = (e) => {\n const data = new Uint8Array(e.target.result);\n const workbook = XLSX.read(data, { type: 'array' });\n const sheetName = workbook.SheetNames[0];\n const sheet = workbook.Sheets[sheetName];\n employeeData = XLSX.utils.sheet_to_json(sheet, { header: 1 });\n renderTable(employeeData);\n };\n reader.readAsArrayBuffer(file);\n }\n\n function renderTable(data) {\n tableHeader.innerHTML = '';\n tableBody.innerHTML = '';\n\n if (data.length === 0) return;\n\n const headers = data[0];\n headers.push('Service Length');\n\n headers.forEach(header => {\n const th = document.createElement('th');\n th.textContent = header;\n tableHeader.appendChild(th);\n });\n\n data.slice(1).forEach(row => {\n const tr = document.createElement('tr');\n row.forEach((cell, index) => {\n const td = document.createElement('td');\n if (index === 1 && typeof cell === 'number') {\n td.textContent = formatExcelDate(cell);\n row[index] = formatExcelDate(cell); \/\/ Update the raw data for proper export\n } else {\n td.textContent = cell;\n }\n tr.appendChild(td);\n });\n const td = document.createElement('td');\n td.textContent = '';\n tr.appendChild(td);\n tableBody.appendChild(tr);\n });\n }\n\n function calculateServiceLength() {\n const calcDate = new Date(calcDateInput.value);\n if (!calcDateInput.value) {\n alert('Please select a calculation date.');\n return;\n }\n\n employeeData.slice(1).forEach((row, index) => {\n let joinDate = row[1];\n if (typeof joinDate === 'string') {\n joinDate = new Date(joinDate.split('-').reverse().join('-'));\n } else if (typeof joinDate === 'number') {\n joinDate = convertExcelDate(joinDate);\n }\n\n const startDate = new Date(joinDate);\n\n \/\/ Ensure valid date\n if (isNaN(startDate.getTime())) {\n tableBody.rows[index].cells[2].textContent = 'Invalid Date';\n return;\n }\n\n let years = calcDate.getFullYear() - startDate.getFullYear();\n let months = calcDate.getMonth() - startDate.getMonth();\n let days = calcDate.getDate() - startDate.getDate();\n\n if (days < 0) {\n months -= 1;\n const prevMonth = new Date(calcDate.getFullYear(), calcDate.getMonth(), 0);\n days += prevMonth.getDate();\n }\n\n if (months < 0) {\n years -= 1;\n months += 12;\n }\n\n let serviceLength = '';\n if (years > 0) {\n serviceLength = `${years} years, ${months} months, ${days} days`;\n } else if (months > 0) {\n serviceLength = `0 years, ${months} months, ${days} days`;\n } else {\n serviceLength = `0 years, 0 months, ${days} days`;\n }\n\n row[2] = serviceLength;\n tableBody.rows[index].cells[2].textContent = serviceLength;\n });\n\n exportButton.disabled = false;\n }\n\n function convertExcelDate(serial) {\n const excelEpoch = new Date(1899, 11, 30); \/\/ Excel epoch starts from 30 Dec 1899\n return new Date(excelEpoch.getTime() + serial * 86400000);\n }\n\n function formatExcelDate(serial) {\n const date = convertExcelDate(serial);\n return `${String(date.getDate()).padStart(2, '0')}-${String(date.getMonth() + 1).padStart(2, '0')}-${date.getFullYear()}`;\n }\n\n function exportToExcel() {\n const worksheet = XLSX.utils.aoa_to_sheet(employeeData);\n const workbook = XLSX.utils.book_new();\n XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');\n XLSX.writeFile(workbook, 'Employee_Service_Length.xlsx');\n }\n <\/script>\n<\/body>\n<\/html>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>Do you need something simple? Just want to enter a few dates and want to know what will be their service length <a href=\"https:\/\/ashrafimahbub.com\/?p=1208\">CLICK HERE.<\/a><\/p>\n<div class=\"fb-background-color\">\n\t\t\t <div \n\t\t\t \tclass = \"fb-comments\" \n\t\t\t \tdata-href = \"https:\/\/ashrafimahbub.com\/?p=1181\"\n\t\t\t \tdata-numposts = \"10\"\n\t\t\t \tdata-lazy = \"true\"\n\t\t\t\tdata-colorscheme = \"light\"\n\t\t\t\tdata-order-by = \"social\"\n\t\t\t\tdata-mobile=true>\n\t\t\t <\/div><\/div>\n\t\t <style>\n\t\t .fb-background-color {\n\t\t\t\tbackground: #ffffff !important;\n\t\t\t}\n\t\t\t.fb_iframe_widget_fluid_desktop iframe {\n\t\t\t width: 320px !important;\n\t\t\t}\n\t\t <\/style>\n\t\t ","protected":false},"excerpt":{"rendered":"<p>Employee Service Length Calculator Employee Service Length Calculator With Excel Data Import & Export Please choose an Excel file where the first coloumn contains ‘Employee ID’ and the second coloumn contains ‘Date of Joining’. Do not add any other coloumn. After exporting the calculation to Excel file you may use XLOOKUP or VLOOKUP to match […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,263],"tags":[307,306,310,317,308,316,312,314,305,311,315,309,313,319,318],"class_list":["post-1181","post","type-post","status-publish","format-standard","hentry","category-all","category-hr-analytics","tag-calculate-service-length","tag-employee-calculator","tag-employee-excel-data","tag-employee-management-tool","tag-employee-service-length","tag-employee-service-tracking","tag-excel-date-conversion","tag-export-service-length-to-excel","tag-html-service-calculator","tag-joining-date-calculation","tag-service-length-calculation-tool","tag-service-length-export","tag-wordpress-custom-html","tag-wordpress-employee-tool","tag-wordpress-table-script"],"_links":{"self":[{"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/posts\/1181","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1181"}],"version-history":[{"count":26,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/posts\/1181\/revisions"}],"predecessor-version":[{"id":1223,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/posts\/1181\/revisions\/1223"}],"wp:attachment":[{"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1181"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1181"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1181"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

{"id":1181,"date":"2024-12-30T09:41:28","date_gmt":"2024-12-30T03:41:28","guid":{"rendered":"https:\/\/ashrafimahbub.com\/?p=1181"},"modified":"2024-12-31T17:02:14","modified_gmt":"2024-12-31T11:02:14","slug":"employee-service-lenght-calculator","status":"publish","type":"post","link":"https:\/\/ashrafimahbub.com\/?p=1181","title":{"rendered":"Employee Service Lenght Calculator"},"content":{"rendered":"\n\n\n\n \n \n Employee Service Length Calculator<\/title>\n <script src=\"https:\/\/cdnjs.cloudflare.com\/ajax\/libs\/xlsx\/0.18.5\/xlsx.full.min.js\"><\/script>\n <script src=\"https:\/\/cdnjs.cloudflare.com\/ajax\/libs\/FileSaver.js\/2.0.5\/FileSaver.min.js\"><\/script>\n <style>\n body {\n font-family: Arial, sans-serif;\n padding: 20px;\n margin: 0;\n }\n\n h1 {\n text-align: center;\n }\n\n table {\n width: 100%;\n margin-top: 20px;\n border-collapse: collapse;\n }\n\n table, th, td {\n border: 1px solid #ddd;\n text-align: left;\n }\n\n th, td {\n padding: 10px;\n }\n\n button, input[type=\"file\"], input[type=\"date\"] {\n margin: 10px 0;\n padding: 10px;\n font-size: 16px;\n }\n\n label {\n display: block;\n margin: 10px 0;\n }\n <\/style>\n<\/head>\n<body>\n <h1>Employee Service Length Calculator With Excel Data Import & Export<\/h1>\n\n <label for='fileInput'>Please choose an Excel file where the first coloumn contains 'Employee ID' and the second coloumn contains 'Date of Joining'. Do not add any other coloumn. After exporting the calculation to Excel file you may use XLOOKUP or VLOOKUP to match with large data if required.<\/label>\n <input type=\"file\" id=\"fileInput\" accept=\".xlsx, .xls\">\n <br>\n\n <label for=\"calcDate\">Calculate service length as of:<\/label>\n <input type=\"date\" id=\"calcDate\">\n <br>\n\n <button id=\"calculateButton\">Calculate Service Length<\/button>\n <button id=\"exportButton\" disabled>Export to Excel<\/button>\n\n <table id=\"dataTable\">\n <thead>\n <tr id=\"tableHeader\"><\/tr>\n <\/thead>\n <tbody id=\"tableBody\"><\/tbody>\n <\/table>\n\n <script>\n const fileInput = document.getElementById('fileInput');\n const calcDateInput = document.getElementById('calcDate');\n const calculateButton = document.getElementById('calculateButton');\n const exportButton = document.getElementById('exportButton');\n const tableHeader = document.getElementById('tableHeader');\n const tableBody = document.getElementById('tableBody');\n let employeeData = [];\n\n fileInput.addEventListener('change', handleFile);\n calculateButton.addEventListener('click', calculateServiceLength);\n exportButton.addEventListener('click', exportToExcel);\n\n function handleFile(event) {\n const file = event.target.files[0];\n if (!file) return;\n\n const reader = new FileReader();\n reader.onload = (e) => {\n const data = new Uint8Array(e.target.result);\n const workbook = XLSX.read(data, { type: 'array' });\n const sheetName = workbook.SheetNames[0];\n const sheet = workbook.Sheets[sheetName];\n employeeData = XLSX.utils.sheet_to_json(sheet, { header: 1 });\n renderTable(employeeData);\n };\n reader.readAsArrayBuffer(file);\n }\n\n function renderTable(data) {\n tableHeader.innerHTML = '';\n tableBody.innerHTML = '';\n\n if (data.length === 0) return;\n\n const headers = data[0];\n headers.push('Service Length');\n\n headers.forEach(header => {\n const th = document.createElement('th');\n th.textContent = header;\n tableHeader.appendChild(th);\n });\n\n data.slice(1).forEach(row => {\n const tr = document.createElement('tr');\n row.forEach((cell, index) => {\n const td = document.createElement('td');\n if (index === 1 && typeof cell === 'number') {\n td.textContent = formatExcelDate(cell);\n row[index] = formatExcelDate(cell); \/\/ Update the raw data for proper export\n } else {\n td.textContent = cell;\n }\n tr.appendChild(td);\n });\n const td = document.createElement('td');\n td.textContent = '';\n tr.appendChild(td);\n tableBody.appendChild(tr);\n });\n }\n\n function calculateServiceLength() {\n const calcDate = new Date(calcDateInput.value);\n if (!calcDateInput.value) {\n alert('Please select a calculation date.');\n return;\n }\n\n employeeData.slice(1).forEach((row, index) => {\n let joinDate = row[1];\n if (typeof joinDate === 'string') {\n joinDate = new Date(joinDate.split('-').reverse().join('-'));\n } else if (typeof joinDate === 'number') {\n joinDate = convertExcelDate(joinDate);\n }\n\n const startDate = new Date(joinDate);\n\n \/\/ Ensure valid date\n if (isNaN(startDate.getTime())) {\n tableBody.rows[index].cells[2].textContent = 'Invalid Date';\n return;\n }\n\n let years = calcDate.getFullYear() - startDate.getFullYear();\n let months = calcDate.getMonth() - startDate.getMonth();\n let days = calcDate.getDate() - startDate.getDate();\n\n if (days < 0) {\n months -= 1;\n const prevMonth = new Date(calcDate.getFullYear(), calcDate.getMonth(), 0);\n days += prevMonth.getDate();\n }\n\n if (months < 0) {\n years -= 1;\n months += 12;\n }\n\n let serviceLength = '';\n if (years > 0) {\n serviceLength = `${years} years, ${months} months, ${days} days`;\n } else if (months > 0) {\n serviceLength = `0 years, ${months} months, ${days} days`;\n } else {\n serviceLength = `0 years, 0 months, ${days} days`;\n }\n\n row[2] = serviceLength;\n tableBody.rows[index].cells[2].textContent = serviceLength;\n });\n\n exportButton.disabled = false;\n }\n\n function convertExcelDate(serial) {\n const excelEpoch = new Date(1899, 11, 30); \/\/ Excel epoch starts from 30 Dec 1899\n return new Date(excelEpoch.getTime() + serial * 86400000);\n }\n\n function formatExcelDate(serial) {\n const date = convertExcelDate(serial);\n return `${String(date.getDate()).padStart(2, '0')}-${String(date.getMonth() + 1).padStart(2, '0')}-${date.getFullYear()}`;\n }\n\n function exportToExcel() {\n const worksheet = XLSX.utils.aoa_to_sheet(employeeData);\n const workbook = XLSX.utils.book_new();\n XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');\n XLSX.writeFile(workbook, 'Employee_Service_Length.xlsx');\n }\n <\/script>\n<\/body>\n<\/html>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p>Do you need something simple? Just want to enter a few dates and want to know what will be their service length <a href=\"https:\/\/ashrafimahbub.com\/?p=1208\">CLICK HERE.<\/a><\/p>\n<div class=\"fb-background-color\">\n\t\t\t <div \n\t\t\t \tclass = \"fb-comments\" \n\t\t\t \tdata-href = \"https:\/\/ashrafimahbub.com\/?p=1181\"\n\t\t\t \tdata-numposts = \"10\"\n\t\t\t \tdata-lazy = \"true\"\n\t\t\t\tdata-colorscheme = \"light\"\n\t\t\t\tdata-order-by = \"social\"\n\t\t\t\tdata-mobile=true>\n\t\t\t <\/div><\/div>\n\t\t <style>\n\t\t .fb-background-color {\n\t\t\t\tbackground: #ffffff !important;\n\t\t\t}\n\t\t\t.fb_iframe_widget_fluid_desktop iframe {\n\t\t\t width: 320px !important;\n\t\t\t}\n\t\t <\/style>\n\t\t ","protected":false},"excerpt":{"rendered":"<p>Employee Service Length Calculator Employee Service Length Calculator With Excel Data Import & Export Please choose an Excel file where the first coloumn contains ‘Employee ID’ and the second coloumn contains ‘Date of Joining’. Do not add any other coloumn. After exporting the calculation to Excel file you may use XLOOKUP or VLOOKUP to match […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,263],"tags":[307,306,310,317,308,316,312,314,305,311,315,309,313,319,318],"class_list":["post-1181","post","type-post","status-publish","format-standard","hentry","category-all","category-hr-analytics","tag-calculate-service-length","tag-employee-calculator","tag-employee-excel-data","tag-employee-management-tool","tag-employee-service-length","tag-employee-service-tracking","tag-excel-date-conversion","tag-export-service-length-to-excel","tag-html-service-calculator","tag-joining-date-calculation","tag-service-length-calculation-tool","tag-service-length-export","tag-wordpress-custom-html","tag-wordpress-employee-tool","tag-wordpress-table-script"],"_links":{"self":[{"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/posts\/1181","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1181"}],"version-history":[{"count":26,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/posts\/1181\/revisions"}],"predecessor-version":[{"id":1223,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=\/wp\/v2\/posts\/1181\/revisions\/1223"}],"wp:attachment":[{"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1181"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1181"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ashrafimahbub.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1181"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}