![\"Sample](\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png\")
<\/a><\/p>\nNote<\/strong>: In cell I15<\/code> I am using the idea suggested in the answer to the question: sequence a sumif with exclusions in Excel provided by me for checking that the intervals of the roster data set don’t overlap.<\/p>\nUPDATE<\/h2>\n
The formula can be simplified as follow. There is no need to define a user LAMBDA<\/code> function: SPLIT<\/code>. We can make the MAP<\/code> function to return only the index position of the overlaps, and then use this information to find the rest of the information. We use also DROP\/REDUCE\/VSTACK\/HSTACK<\/code> combination to return multiple values (we were limited by MAP<\/code> on returning only a single column)<\/p>\n=LET(setPrj, A2:E12, setRoster, A15:F33, SOMs, I1:T1,namesPrj, INDEX(setPrj,,1),\n startsPrj, INDEX(setPrj,,4), endsPrj,INDEX(setPrj,,5),names, INDEX(setRoster,,1),\n starts, INDEX(setRoster,,2), ends, INDEX(setRoster,,3), salaries,INDEX(setRoster,,6),\n BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM, 0), endsAdj, IF(ends > 0, ends, EOM),\n overlapsPrj, MAP(SEQUENCE(ROWS(setPrj)), startsPrj, endsPrj,\n LAMBDA(idx, startPrj, endPrj,IF(AND(startPrj <= EOM, endPrj >= SOM),\n idx, 0))), pIdx, FILTER(overlapsPrj, overlapsPrj > 0, NA()),\n overlapsRoster, IF(ISNA(ROWS(pIdx)), NA(), DROP(REDUCE(0,pIdx,\n LAMBDA(acc, idx, VSTACK(acc, LET(name, INDEX(namesPrj,idx),\n start, MAX(INDEX(startsPrj,idx), SOM), end, MIN(INDEX(endsPrj,idx), EOM),\n found, FILTER(HSTACK(starts, endsAdj, salaries), (names=name) * (starts <= end)\n * (endsAdj >= start), NA()),\n IF(AND(NOT(ISNA(found)), ROWS(found)=1), HSTACK(MAX(start, INDEX(found,1,1)),\n MIN(end, INDEX(found,1,2)), INDEX(found,1,3)),{0,0,0})\n )))),1)), rEff, FILTER(overlapsRoster, INDEX(overlapsRoster,,3) > 0, NA()),\n IF(ISNA(ROWS(rEff)),0, LET(effStarts, INDEX(rEff,,1), effEnds,\n INDEX(rEff,,2), effSalaries, INDEX(rEff,,3),days, NETWORKDAYS(effStarts, effEnds),\n SUMPRODUCT(days, effSalaries)\n )))))\n )\n<\/code><\/pre>\nExplanation<\/h2>\nGeneral Idea<\/h3>\n
For the calculation we use two main ideas:<\/p>\n
\n- \n
The condition for checking if two intervals (A<\/code>,B<\/code>) overlap:<\/p>\n(StartA <= EndB) AND (EndA >= StartB) -> TRUE<\/code><\/p>\n<\/li>\n- \n
If two intervals A<\/code>,B<\/code> overlap, the intersection of both intervals are:<\/p>\n[MAX(StartA, StartB), MIN(EndA, EndB)]<\/code><\/p>\n<\/li>\n<\/ol>\nWe are going to iterate through each month. First look into the project dataset for interval overlaps on a given month, and for each interval that overlaps with the current month to find the intersection.<\/p>\n
Once we have the list of resources with their corresponding intersections. We need to find the corresponding salary that matches the interval. We iterate again for the intersections found in the project dataset but now looking for a second intersection in the salary dataset.<\/p>\n
Formula<\/h3>\n
We use LET<\/code> function to define the inputs and intermediate results. We start defining two datasets setPrj<\/code> for the data representing the project information and setRoster<\/code> for the Roster information and related names required from the input set.<\/p>\nWe are going to use MAP<\/code> function (for finding each overlap) which is very convenient for doing transformations, it can take several input arrays of the same size, but<\/strong> then it returns a single array. To circumvent this, the output of MAP<\/code> will be an nx1<\/code> array and on each row, the information will be a string with comma-separated values (CSV). We define inside of LET<\/code> a user custom LAMBDA<\/code> function to convert the result back from CSV format to a 2D array.<\/p>\nThis user function is named SPLIT<\/code> (don’t confuse it with TEXTSPLIT<\/code> standard Excel function). Defining this function inside of LET<\/code> function, the scope is limited to that function and there is no need to create a named range for that.<\/p>\nSPLIT, LAMBDA(x,case, LET(y, TEXTSPLIT(TEXTJOIN(\";\",,x),\",\",\";\"),\n z, FILTER(y, INDEX(y,,1)<>\"\", NA()),\n IFS(case=0, z,case=1, HSTACK(INDEX(z,,1),\n 1*CHOOSECOLS(z,2,3)), case=2, 1*z)))\n<\/code><\/pre>\nBecause the input argument x<\/code> will be an nx1<\/code> array of comma-separated values, once we convert it to a 2D array, we need to convert some columns back to their original data type. We use the second input argument case<\/code>, to consider all cast scenarios used in the main formula.<\/p>\n\ncase=1<\/code>, converts columns 2<\/code> and 3<\/code> to numbers<\/li>\ncase=2<\/code>, converts all columns to numbers<\/li>\n<\/ul>\nNote<\/strong>: case=0<\/code>, is not used in the main formula, provided for testing purposes only. The MAP<\/code> output will be in both calls an array of three columns.<\/p>\nFinally, the output will be an nxm<\/code> array that has been removed the empty rows (\",,\"<\/code>. There is a name defined for that: empty<\/code>). In case all rows are empty, FILTER<\/code> will return an error (the empty set doesn’t exist in Excel), to prevent that we use the third input argument of this function to return NA()<\/code>(we use the same idea in other parts of the main formula)<\/p>\nNow we use BYCOL<\/code> to iterate over the months (we use this function because the months are in column format). For each month (represented as the first day of the month) we use SOM<\/code>(Start of the month) and EOM<\/code> (End of the Month) names to find the overlaps and intersections. The first MAP<\/code> call does it and the result is named intersecPrj<\/code>:<\/p>\nMAP(namesPrj, startsPrj, endsPrj, LAMBDA(namePrj,startPrj,endPrj,\n IF(AND(startPrj <= EOM, endPrj >= SOM),\n TEXTJOIN(\",\",,namePrj, MAX(startPrj, SOM), MIN(endPrj, EOM)), empty)))\n<\/code><\/pre>\nNote<\/strong>: Here we can use FILTER<\/code> instead of MAP<\/code>, but with the latter we can find the overlap and the intersection at the same time. The result is stored in CSV format by row with the following information: name, startDate, endDate<\/code> where the dates represent the intersection dates.<\/p>\nNow we convert back the information to a 2D array via the SPLIT<\/code> function: SPLIT(intersecPrj,1)<\/code>, because we want to keep the name<\/code> as text we use case=1<\/code> and name it as: setPrjAdj<\/code> it is an array nx3<\/code> where n<\/code> represents the number of intersections found.<\/p>\nNow we need to find the corresponding salaries for the names in setPrjAdj<\/code>. Here we need to consider the scenario where no intersection was found, i.e. for a given month, there are no projects with resources associated. The condition for calculating intersecRoster<\/code> prevents that:<\/p>\nIF(ISNA(ROWS(setRosterAdj)), 0,\u2026)\n<\/code><\/pre>\nWe can check against NA()<\/code> because our SPLIT<\/code> function returns this value in case of no intersections, so if the condition is TRUE<\/code> we return NA()<\/code>. If the input of ISNA<\/code> is an array it returns an array, so we use ROWS<\/code> to reduce the test to a single cell. If any element of the array has a #N\/A<\/code> value the ROWS<\/code> output will be #N\/A<\/code>.<\/p>\nIn case project intersections are found we need to find the salary and the corresponding new intersections between the date information in setPrjAdj<\/code> and the dates in the roster dataset.<\/p>\nThis is done via the following MAP<\/code> call and name the result as intersecRoster<\/code><\/p>\nMAP(INDEX(setPrjAdj,,1), INDEX(setPrjAdj,,2), INDEX(setPrjAdj,,3),\n LAMBDA(name, start, end,\n LET(found, FILTER(HSTACK(starts, endsAdj, salaries), (names=name) * (starts <= end)\n * (endsAdj >= start), NA()), IF(AND(ROWS(found)=1, NOT(ISNA(found))),\n TEXTJOIN(\",\",, MAX(INDEX(found,,1), start),\n MIN(INDEX(found,,2), end), INDEX(found,,3)), empty)\n )))\n<\/code><\/pre>\nFor each name<\/code>, start<\/code>, and end<\/code> (from setPrjAdj<\/code>), HSTACK(starts, endsAdj, salaries)<\/code> is filtered by name<\/code> and look for overlaps.<\/p>\nIt is used endsAdj<\/code>, instead of ends<\/code> original input data because we need to treat the blank end dates. The result is saved in the found<\/code> name. Now we need to check for an empty set of the FILTER<\/code>. The not found condition of the filter (empty set) is represented by the following output NA()<\/code>. It could happen that the name was not found (some bad spelling or the name is missing).<\/p>\nIn case it returns more than one row (it should not happen because the interval for the roster set should not overlap<\/strong>, i.e. an employee cannot have two salaries at the same time). We assign an empty<\/code> row. There is no way to determine the salary, so this resource will not contribute to the Salary Monthly Cost. Otherwise, we build the string via TEXTJOIN<\/code> with the corresponding information: start date, end dates, and the corresponding salary. Where the start and end dates represent the intersection between start<\/code> and end<\/code> dates (from setPrjAdj<\/code>) and the start and end dates from the roster dataset (coming from the FILTER<\/code> output).<\/p>\nNow intersecRoster<\/code> has the following information in CSV format: start, end, salary<\/code>. We do the same operation now to convert the string information into 2D-array, via SPLIT<\/code> and name the result setRosterAdj<\/code>. We use the case=2<\/code>, because all the information is numbers.<\/p>\nSPLIT(intersecRoster,2)\n<\/code><\/pre>\nHere we need to prevent the name was not found in the roster table, to avoid any unexpected result. In case no resource was found, then we return 0<\/code> via the following condition:<\/p>\nIF(ISNA(ROWS(setRosterAdj)), 0,\u2026)\n<\/code><\/pre>\nNow we have all the information we are looking for. For calculating the working dates we use NETWORKDAYS(startEffDates, endEffDates)<\/code> where the dates are the corresponding columns from setRosterAdj<\/code> and it is named as days<\/code>.<\/p>\nFinally:<\/p>\n
SUMPRODUCT(days, effSalaries)\n<\/code><\/pre>\nProvides the result we are looking for. We named all the columns from setRosterAdj<\/code> using Eff<\/code> (effective) in the name of the corresponding column.<\/p>\nMonthly Salary for non-partial allocation<\/h3>\n
The previous approach calculates the cost based on working days and the daily salary. In case you will like to consider monthly cost instead for months when the resource was allocated the entire month and daily salary for partially allocated months, here is the adjusted formula:<\/p>\n
=LET(setPrj, A2:E12, setRoster, A15:F33, SOMs, I1:T1, namesPrj, INDEX(setPrj,,1),\n startsPrj, INDEX(setPrj,,4), endsPrj,INDEX(setPrj,,5),names, INDEX(setRoster,,1),\n starts, INDEX(setRoster,,2), ends, INDEX(setRoster,,3),\n monthlySalaries,INDEX(setRoster,,5), dalySalaries,INDEX(setRoster,,6), empty, \",,\",\n SPLIT, LAMBDA(x,case, LET(y, TEXTSPLIT(TEXTJOIN(\";\",,x),\",\",\";\"),\n z, FILTER(y, INDEX(y,,1)<>\"\", NA()),IFS(case=0, z,case=1, HSTACK(INDEX(z,,1),\n 1*CHOOSECOLS(z,2,3)), case=2, 1*z))),\n BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),endsAdj, IF(ends > 0, ends, EOM),\n overlapsPrj, MAP(namesPrj, startsPrj, endsPrj, LAMBDA(namePrj,startPrj,endPrj,\n IF(AND(startPrj <= EOM, endPrj >= SOM), TEXTJOIN(\",\",,namePrj, MAX(startPrj, SOM),\n MIN(endPrj, EOM)), empty))),\n setPrjAdj, SPLIT(overlapsPrj,1),\n overlapsRoster, IF(ISNA(ROWS(setPrjAdj)), NA(),\n MAP(INDEX(setPrjAdj,,1), INDEX(setPrjAdj,,2), INDEX(setPrjAdj,,3),\n LAMBDA(name,start,end,\n LET(found, FILTER(HSTACK(starts, endsAdj, dalySalaries, monthlySalaries),\n (names=name) * (starts <= end) * (endsAdj >= start), NA()),\n IF(AND(ROWS(found)=1, NOT(ISNA(found))),\n TEXTJOIN(\",\",, MAX(INDEX(found,,1), start), MIN(INDEX(found,,2), end),\n CHOOSECOLS(found,3,4)), empty)\n )))\n ),setRosterAdj, SPLIT(overlapsRoster,2),\n IF(ISNA(ROWS(setRosterAdj)), 0,\n LET(startEffDates, INDEX(setRosterAdj,,1), endEffDates, INDEX(setRosterAdj,,2),\n effDailySalaries, INDEX(setRosterAdj,,3), effMonthlySalaries, INDEX(setRosterAdj,,4),\n days, NETWORKDAYS(startEffDates, endEffDates), monthWorkDays, NETWORKDAYS(SOM, EOM),\n actualSalaries, IF(days = monthWorkDays, effMonthlySalaries, effDailySalaries),\n actualDays, IF(days = monthWorkDays, 1, days),\n SUMPRODUCT(actualDays, actualSalaries)\n ))\n )))\n)\n<\/code><\/pre>\nTip<\/h3>\n
Since it is a large formula, and Excel doesn’t provide a way to debug properly some array functions, it is helpful to have a way to debug some of the partial results. Because BYCOL<\/code> returns a cell per column, it is convenient to define another user’s LAMBDA<\/code> function for this purpose inside of the LET<\/code>. For example the following one and name it DEBUG<\/code>.<\/p>\nLAMBDA(x, TEXTJOIN(\" & \",,x)),\n<\/code><\/pre>\nthen it can be used to return the output of DEBUG<\/code> instead of the final result for testing purposes.<\/p>\n<\/p><\/div>\n<\/div>\n<\/div>\n
0<\/span> <\/p><\/div>\n<\/div>\n[ad_2]<\/p>\n
solved Calculating partial months salary costings for employees on a project in Excel [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"
[ad_1] In cell I2, put the following formula (see UPDATE section for a simplified version): =LET(setPrj, A2:E12, setRoster, A15:F33, SOMs, I1:T1, namesPrj, INDEX(setPrj,,1), startsPrj, INDEX(setPrj,,4), endsPrj, INDEX(setPrj,,5),names, INDEX(setRoster,,1),starts, INDEX(setRoster,,2), ends, INDEX(setRoster,,3), salaries,INDEX(setRoster,,6), empty, “,,”, SPLIT, LAMBDA(x,case, LET(y, TEXTSPLIT(TEXTJOIN(“;”,,x),”,”,”;”), z, FILTER(y, INDEX(y,,1)<>””, NA()), IFS(case=0, z,case=1, HSTACK(INDEX(z,,1), 1*CHOOSECOLS(z,2,3)), case=2, 1*z))), BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),endsAdj, IF(ends > 0, … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,1046],"class_list":["post-29689","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-excel-formula"],"yoast_head":"\n[Solved] Calculating partial months salary costings for employees on a project in Excel [closed] - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Calculating partial months salary costings for employees on a project in Excel [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] In cell I2, put the following formula (see UPDATE section for a simplified version): =LET(setPrj, A2:E12, setRoster, A15:F33, SOMs, I1:T1, namesPrj, INDEX(setPrj,,1), startsPrj, INDEX(setPrj,,4), endsPrj, INDEX(setPrj,,5),names, INDEX(setRoster,,1),starts, INDEX(setRoster,,2), ends, INDEX(setRoster,,3), salaries,INDEX(setRoster,,6), empty, ",,", SPLIT, LAMBDA(x,case, LET(y, TEXTSPLIT(TEXTJOIN(";",,x),",",";"), z, FILTER(y, INDEX(y,,1)<>"", NA()), IFS(case=0, z,case=1, HSTACK(INDEX(z,,1), 1*CHOOSECOLS(z,2,3)), case=2, 1*z))), BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),endsAdj, IF(ends > 0, ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2023-01-10T02:52:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Calculating partial months salary costings for employees on a project in Excel [closed]\",\"datePublished\":\"2023-01-10T02:52:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/\"},\"wordCount\":1274,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png\",\"keywords\":[\"excel\",\"excel-formula\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/\",\"name\":\"[Solved] Calculating partial months salary costings for employees on a project in Excel [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png\",\"datePublished\":\"2023-01-10T02:52:48+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#primaryimage\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Calculating partial months salary costings for employees on a project in Excel [closed]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Calculating partial months salary costings for employees on a project in Excel [closed] - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Calculating partial months salary costings for employees on a project in Excel [closed] - JassWeb","og_description":"[ad_1] In cell I2, put the following formula (see UPDATE section for a simplified version): =LET(setPrj, A2:E12, setRoster, A15:F33, SOMs, I1:T1, namesPrj, INDEX(setPrj,,1), startsPrj, INDEX(setPrj,,4), endsPrj, INDEX(setPrj,,5),names, INDEX(setRoster,,1),starts, INDEX(setRoster,,2), ends, INDEX(setRoster,,3), salaries,INDEX(setRoster,,6), empty, \",,\", SPLIT, LAMBDA(x,case, LET(y, TEXTSPLIT(TEXTJOIN(\";\",,x),\",\",\";\"), z, FILTER(y, INDEX(y,,1)<>\"\", NA()), IFS(case=0, z,case=1, HSTACK(INDEX(z,,1), 1*CHOOSECOLS(z,2,3)), case=2, 1*z))), BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0),endsAdj, IF(ends > 0, ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/","og_site_name":"JassWeb","article_published_time":"2023-01-10T02:52:48+00:00","og_image":[{"url":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png","type":"","width":"","height":""}],"author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Calculating partial months salary costings for employees on a project in Excel [closed]","datePublished":"2023-01-10T02:52:48+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/"},"wordCount":1274,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"image":{"@id":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#primaryimage"},"thumbnailUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png","keywords":["excel","excel-formula"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/","name":"[Solved] Calculating partial months salary costings for employees on a project in Excel [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"primaryImageOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#primaryimage"},"image":{"@id":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#primaryimage"},"thumbnailUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png","datePublished":"2023-01-10T02:52:48+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#primaryimage","url":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2023\/01\/Solved-Calculating-partial-months-salary-costings-for-employees-on-a.png"},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-calculating-partial-months-salary-costings-for-employees-on-a-project-in-excel-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Calculating partial months salary costings for employees on a project in Excel [closed]"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/29689","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=29689"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/29689\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=29689"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=29689"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=29689"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}