Count the number of days from date of arrival to current date

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
346
Office Version
  1. 365
Platform
  1. Windows
Sirs,

i have a long range of table with dates as column header.. i want to count the number of days an employee stays in the company. starting the date of arrival up to current date. However, need to consider if the employee return within 5 days after the recent travel out. Please take a look below table for reference. many thanks..


LOOKUP XMATCH AliGW.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBI
2FORMULA HEREFEBRUARY 2023MARCH 2023
3# of days (from arrival to current date)0102030405060708091011121314151617181920212223242526272801020304050607080910111213141516171819202122232425262728293031
4Adams14FT8888888TFFFFFFFFFFFFT8888888888888
5Baker7FT88888888888TFFFFFFFFFFFFFFFT888888
6ClarkFFFFFFFFFFFFFFFFFFFFT888888888TFFFFF
7Davis14T888TFFFFFFFFFFFFFT88888TFFFFT888888
8Evans118888888888TFFFFFFFFFFFT88888TFFFT888
9Frank98888888888TFFFFFFFFFFFT88888TFFFFFT8
10GhoshFFFFFFFFFFFFT8888888TFFFFFFFFFFFFFFF
11Hills22FFFFFFFFFFFFT888888888888888TFFT8888
12
13
14FORMULA
15"T" FOLLOWED BY "F"MEANS TRAVEL OUT
16"T" FOLLOWED BY "8"MEANS TRAVEL IN
178WORK
18FVACATION
19NUMBER OF DAYS TO BE COUNTED
20
21NOTE: BELOW EMPLOYEE RETURN FOR WORK WITHIN 5DAYS AFTER THE RECENT TRAVEL
22Davis
23Evans
24Frank
25Hills
26
27I WOULD LIKE TO COUNT THE NUMBER OF DAYS WHEN AN EMPLOYEE RETURN TO THE FIELD IN WHICH "T" START THE COUNT
28TO ADD THE NUMBER OF DAYS WORK FROM THE RECENT TRAVEL IF THE EMPLOYEE RETURN WITHIN 5 DAYS AFTER THE TRAVEL OUT "T"
Sheet1
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1Adams14FT8888888TFFFFFFFFFFFFT8888888888888
2Baker7FT88888888888TFFFFFFFFFFFFFFFT888888
3ClarkFFFFFFFFFFFFFFFFFFFFT888888888TFFFFF
4Davis14T888TFFFFFFFFFFFFFT88888TFFFFT888888
5Evans118888888888TFFFFFFFFFFFT88888TFFFT888
6Frank98888888888TFFFFFFFFFFFT88888TFFFFFT8
7GhoshFFFFFFFFFFFFT8888888TFFFFFFFFFFFFFFF
8Hills22FFFFFFFFFFFFT888888888888888TFFT8888
Sheet1
Cell Formulas
RangeFormula
B1:B8B1=BYROW(1:8,LAMBDA(r,LET(x,CONCAT("T",DROP(r,,2)),IF(RIGHT(x)="8",LET(y,TEXTSPLIT(x,"T",,1),REDUCE(0,SEQUENCE(COUNTA(y)),LAMBDA(a,b,LET(z,INDEX(y,,b),IF(AND(LEFT(z)="F",LEN(z)<6),a,IF(LEFT(z)="8",a+LEN(z)+1+(b<COUNTA(y)),0)))))),""))))
Dynamic array formulas.
 
Last edited:
Upvote 0
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1Adams14FT8888888TFFFFFFFFFFFFT8888888888888
2Baker7FT88888888888TFFFFFFFFFFFFFFFT888888
3ClarkFFFFFFFFFFFFFFFFFFFFT888888888TFFFFF
4Davis14T888TFFFFFFFFFFFFFT88888TFFFFT888888
5Evans118888888888TFFFFFFFFFFFT88888TFFFT888
6Frank98888888888TFFFFFFFFFFFT88888TFFFFFT8
7GhoshFFFFFFFFFFFFT8888888TFFFFFFFFFFFFFFF
8Hills22FFFFFFFFFFFFT888888888888888TFFT8888
Sheet1
Cell Formulas
RangeFormula
B1:B8B1=BYROW(1:8,LAMBDA(r,LET(x,CONCAT("T",DROP(r,,2)),IF(RIGHT(x)="8",LET(y,TEXTSPLIT(x,"T",,1),REDUCE(0,SEQUENCE(COUNTA(y)),LAMBDA(a,b,LET(z,INDEX(y,,b),IF(AND(LEFT(z)="F",LEN(z)<6),a,IF(LEFT(z)="8",a+LEN(z)+1+(b<COUNTA(y)),0)))))),""))))
Dynamic array formulas.
thank you sir, it works but we need to consider the dates in the column header.. there are times that i need to put the attendance in advance. Let say, today is 8/3/2023. I need to fill in up to 15/3/2023 or more, depends on the circumstances.. we need to count up to current date only..
 
Upvote 0

Forum statistics

Threads
1,223,970
Messages
6,175,703
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top