topic Re: Duration calculation logic for Ten days period in Product Forum

Duration calculation logic for Ten days period

sandeshtope — Mon, 09 Dec 2024 04:17:28 GMT

I need to calculate the "Ten Days Duration" (TDD) codes for a given date, covering the current, previous, and next 10 TDDs. The TDD format is Year+TDD and follows this cycle from December 21 of each year to December 20 of the next year.

TDD Mapping:

Dec 1 – Dec 10, 2024 → 202435
Dec 11 – Dec 20, 2024 → 202436
Dec 21 – Dec 31, 2024 → 202401
Jan 1 – Jan 10, 2025 → 202502
Jan 11 – Jan 20, 2025 → 202503
Jan 21 – Jan 31, 2025 → 202503

Can anyone suggest something for automating this calculation ?

Re: Duration calculation logic for Ten days period

Nandhakumar — Mon, 09 Dec 2024 07:28:07 GMT

Hi @sandeshtope ,

Can you pls help me to understand the mapping further?

TDD Mapping:

Dec 1 – Dec 10, 2024 → 202435
Dec 11 – Dec 20, 2024 → 202436
Dec 21 – Dec 31, 2024 → 202401 - This shoud be 202501 right?
Jan 1 – Jan 10, 2025 → 202502
Jan 11 – Jan 20, 2025 → 202503
Jan 21 – Jan 31, 2025 → 202503 - This shoud be 202504 right?

also can you pls proide the mapping for couple of months to understand the mapping pattern

Re: Duration calculation logic for Ten days period

SayeedBinAbdullah — Wed, 11 Dec 2024 05:57:07 GMT

Hi @sandeshtope

As per the calculation you have mentioned, if we start from December 21 and end at December 20 then for leap year we will be left with 6 Days at the end, and for the non leap year it will be 5 Days at the end not 10 Days...

Just wanted to know how the calculation should be?
If we go by exact 10 days difference then date range will differ each year it will not remain constant as you mentioned from December 21 to December 20.

If possible please can you share more details as requested by @Nandhakumar

Sayeed Bin Abdullah

Re: Duration calculation logic for Ten days period

sandeshtope — Wed, 11 Dec 2024 15:51:02 GMT

Hi @Nandhakumar & @SayeedBinAbdullah

Below is the TDD Logic

Date Range TDD

01 Dec 2023 to 10 Dec 2023

202335

11 Dec 2023 to 20 Dec 2023

202336

21 Dec 2023 to 31 Dec 2023

202401

01 Jan 2024 to 10 Jan 2024

202402

11 Jan 2024 to 20 Jan 2024

202403

21 Jan 2024 to 31 Jan 2024

202404

01 Feb 2024 to 10 Feb 2024

202405

11 Feb 2024 to 20 Feb 2024

202406

21 Feb 2024 to 29 Feb 2024

202407

01 Mar 2024 to 10 Mar 2024

202408

11 Mar 2024 to 20 Mar 2024

202409

21 Mar 2024 to 31 Mar 2024

202410

01 Apr 2024 to 10 Apr 2024

202411

11 Apr 2024 to 20 Apr 2024

202412

21 Apr 2024 to 30 Apr 2024

202413

01 May 2024 to 10 May 2024

202414

11 May 2024 to 20 May 2024

202415

21 May 2024 to 31 May 2024

202416

01 Jun 2024 to 10 Jun 2024

202417

11 Jun 2024 to 20 Jun 2024

202418

21 Jun 2024 to 30 Jun 2024

202419

01 Jul 2024 to 10 Jul 2024

202420

11 Jul 2024 to 20 Jul 2024

202421

21 Jul 2024 to 31 Jul 2024

202422

01 Aug 2024 to 10 Aug 2024

202423

11 Aug 2024 to 20 Aug 2024

202424

21 Aug 2024 to 31 Aug 2024

202425

01 Sep 2024 to 10 Sep 2024

202426

11 Sep 2024 to 20 Sep 2024

202427

21 Sep 2024 to 30 Sep 2024

202428

01 Oct 2024 to 10 Oct 2024

202429

11 Oct 2024 to 20 Oct 2024

202430

21 Oct 2024 to 31 Oct 2024

202431

01 Nov 2024 to 10 Nov 2024

202432

11 Nov 2024 to 20 Nov 2024

202433

21 Nov 2024 to 30 Nov 2024

202434

01 Dec 2024 to 10 Dec 2024

202435

11 Dec 2024 to 20 Dec 2024

202436

21 Dec 2024 to 31 Dec 2024

202501

01 Jan 2025 to 10 Jan 2025

202502

11 Jan 2025 to 20 Jan 2025

202503

21 Jan 2025 to 31 Jan 2025

202504

Re: Duration calculation logic for Ten days period

sandeshtope — Thu, 12 Dec 2024 09:15:49 GMT

Hi @SayeedBinAbdullah & @Nandhakumar

Thanks for the reply ,

Below is the logic I am looking for

Re: Duration calculation logic for Ten days period

SayeedBinAbdullah — Thu, 12 Dec 2024 13:16:35 GMT

Hi @sandeshtope

Thanks for sharing more details about the problem,

Based on the details you have shared please find below solution,

Assumptions:

The date range specified in the above given image will always remain constant irrespective of year.
The input date will also be in “DD MMM YYYY” format.

Note:

For now the date range have been manually entered in a collection but this can later be moved to an excel and BOT can be configured to read the collection from an excel.
The given logic can still be fine tuned.
Please copy the below code and paste it as a page in the process.

<process name="__selection__TDD Calculation">
<subsheet subsheetid="b01af1ed-09ce-4704-9760-750eefedaa81" type="Normal" published="False">
<name>TDD Calculation</name>
<view>
<camerax>0</camerax>
<cameray>26</cameray>
<zoom version="2">1.25</zoom>
</view>
</subsheet>
<stage stageid="5c745d8e-39f1-42e6-a2f6-d8f6e6f256f3" name="TDD Calculation" type="SubSheetInfo">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-195" y="-120" w="150" h="90" />
</stage>
<stage stageid="8c8f9ad7-b02a-4696-a850-723e9251e26b" name="Start" type="Start">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="75" y="-150" />
<onsuccess>285d9d54-d7bf-4fb4-8c38-e412a87bd7ae</onsuccess>
</stage>
<stage stageid="025ccff3-f2ab-4d74-ac70-9638ac4bcdb8" name="End" type="End">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-45" y="210" />
</stage>
<stage stageid="b2002739-889b-486f-bcd7-58c2cc2c2ea8" name="TDD Collection" type="Collection">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-195" y="-30" w="150" h="30" />
<datatype>collection</datatype>
<private />
<alwaysinit />
<collectioninfo>
<field name="Date Range" type="text" />
<field name="Code" type="text" />
</collectioninfo>
<initialvalue>
<row>
<field name="Date Range" type="text" value="21 Dec to 31 Dec" />
<field name="Code" type="text" value="01" />
</row>
<row>
<field name="Date Range" type="text" value="01 Jan to 10 Jan" />
<field name="Code" type="text" value="02" />
</row>
<row>
<field name="Date Range" type="text" value="11 Jan to 20 Jan" />
<field name="Code" type="text" value="03" />
</row>
<row>
<field name="Date Range" type="text" value="21 Jan to 31 Jan" />
<field name="Code" type="text" value="04" />
</row>
<row>
<field name="Date Range" type="text" value="01 Feb to 10 Feb" />
<field name="Code" type="text" value="05" />
</row>
<row>
<field name="Date Range" type="text" value="11 Feb to 20 Feb" />
<field name="Code" type="text" value="06" />
</row>
<row>
<field name="Date Range" type="text" value="21 Feb to 29 Feb" />
<field name="Code" type="text" value="07" />
</row>
<row>
<field name="Date Range" type="text" value="01 Mar to 10 Mar" />
<field name="Code" type="text" value="08" />
</row>
<row>
<field name="Date Range" type="text" value="11 Mar to 20 Mar" />
<field name="Code" type="text" value="09" />
</row>
<row>
<field name="Date Range" type="text" value="21 Mar to 31 Mar" />
<field name="Code" type="text" value="10" />
</row>
<row>
<field name="Date Range" type="text" value="01 Apr to 10 Apr" />
<field name="Code" type="text" value="11" />
</row>
<row>
<field name="Date Range" type="text" value="11 Apr to 20 Apr" />
<field name="Code" type="text" value="12" />
</row>
<row>
<field name="Date Range" type="text" value="21 Apr to 30 Apr" />
<field name="Code" type="text" value="13" />
</row>
<row>
<field name="Date Range" type="text" value="01 May to 10 May" />
<field name="Code" type="text" value="14" />
</row>
<row>
<field name="Date Range" type="text" value="11 May to 20 May" />
<field name="Code" type="text" value="15" />
</row>
<row>
<field name="Date Range" type="text" value="21 May to 31 May" />
<field name="Code" type="text" value="16" />
</row>
<row>
<field name="Date Range" type="text" value="01 Jun to 10 Jun" />
<field name="Code" type="text" value="17" />
</row>
<row>
<field name="Date Range" type="text" value="11 Jun to 20 Jun" />
<field name="Code" type="text" value="18" />
</row>
<row>
<field name="Date Range" type="text" value="21 Jun to 30 Jun" />
<field name="Code" type="text" value="19" />
</row>
<row>
<field name="Date Range" type="text" value="01 Jul to 10 Jul" />
<field name="Code" type="text" value="20" />
</row>
<row>
<field name="Date Range" type="text" value="11 Jul to 20 Jul" />
<field name="Code" type="text" value="21" />
</row>
<row>
<field name="Date Range" type="text" value="21 Jul to 31 Jul" />
<field name="Code" type="text" value="22" />
</row>
<row>
<field name="Date Range" type="text" value="01 Aug to 10 Aug" />
<field name="Code" type="text" value="23" />
</row>
<row>
<field name="Date Range" type="text" value="11 Aug to 20 Aug" />
<field name="Code" type="text" value="24" />
</row>
<row>
<field name="Date Range" type="text" value="21 Aug to 31 Aug" />
<field name="Code" type="text" value="25" />
</row>
<row>
<field name="Date Range" type="text" value="01 Sep to 10 Sep" />
<field name="Code" type="text" value="26" />
</row>
<row>
<field name="Date Range" type="text" value="11 Sep to 20 Sep" />
<field name="Code" type="text" value="27" />
</row>
<row>
<field name="Date Range" type="text" value="21 Sep to 30 Sep" />
<field name="Code" type="text" value="28" />
</row>
<row>
<field name="Date Range" type="text" value="01 Oct to 10 Oct" />
<field name="Code" type="text" value="29" />
</row>
<row>
<field name="Date Range" type="text" value="11 Oct to 20 Oct" />
<field name="Code" type="text" value="30" />
</row>
<row>
<field name="Date Range" type="text" value="21 Oct to 31 Oct" />
<field name="Code" type="text" value="31" />
</row>
<row>
<field name="Date Range" type="text" value="01 Nov to 10 Nov" />
<field name="Code" type="text" value="32" />
</row>
<row>
<field name="Date Range" type="text" value="11 Nov to 20 Nov" />
<field name="Code" type="text" value="33" />
</row>
<row>
<field name="Date Range" type="text" value="21 Nov to 30 Nov" />
<field name="Code" type="text" value="34" />
</row>
<row>
<field name="Date Range" type="text" value="01 Dec to 10 Dec" />
<field name="Code" type="text" value="35" />
</row>
<row>
<field name="Date Range" type="text" value="11 Dec to 20 Dec" />
<field name="Code" type="text" value="36" />
</row>
</initialvalue>
</stage>
<stage stageid="e9bfdc57-0ab0-4e1d-957a-8df18e47eb4c" name="Utility - Collection Manipulation::Filter Collection" type="Action">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<loginhibit onsuccess="true" />
<display x="75" y="-30" w="90" h="30" />
<inputs>
<input type="collection" name="Collection In" friendlyname="Collection In" narrative="The collection to filter." expr="[TDD Collection]" />
<input type="text" name="Filter" friendlyname="Filter" narrative="The filter query." expr=""[Date Range] like '%"&[MonthCode]&"%'"" />
</inputs>
<outputs>
<output type="collection" name="Collection Out" friendlyname="Collection Out" narrative="The filtered collection." stage="TDD Output Collection" />
</outputs>
<onsuccess>098c2285-dea9-405a-b0e6-78f09e77809e</onsuccess>
<resource object="Utility - Collection Manipulation" action="Filter Collection" />
</stage>
<stage stageid="0cedf7ca-bb40-40d2-b6f4-fa691a83687b" name="Date to Search" type="Data">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-195" y="0" w="150" h="30" />
<datatype>text</datatype>
<initialvalue xml:space="preserve">12 Dec 2024</initialvalue>
<private />
<alwaysinit />
</stage>
<stage stageid="285d9d54-d7bf-4fb4-8c38-e412a87bd7ae" name="Format Date" type="MultipleCalculation">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<loginhibit onsuccess="true" />
<display x="75" y="-90" w="90" h="30" />
<onsuccess>e9bfdc57-0ab0-4e1d-957a-8df18e47eb4c</onsuccess>
<steps>
<calculation expression="FormatDate(ToDate([Date to Search]),"yyyy")
" stage="YearCode" />
<calculation expression="FormatDate(ToDate([Date to Search]),"MMM")" stage="MonthCode" />
<calculation expression="FormatDate(ToDate([Date to Search]),"dd")" stage="DateCode" />
</steps>
</stage>
<stage stageid="4259fe88-aaec-43b7-a221-84bc66cb0756" name="YearCode" type="Data">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-195" y="180" w="150" h="30" />
<datatype>text</datatype>
<initialvalue />
<private />
<alwaysinit />
</stage>
<stage stageid="5844d504-9012-4491-b772-88615a1039fc" name="DateCode" type="Data">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-195" y="120" w="150" h="30" />
<datatype>text</datatype>
<initialvalue />
<private />
<alwaysinit />
</stage>
<stage stageid="a4b9f0cd-33fc-4c31-b46b-6f45a2e37927" name="MonthCode" type="Data">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-195" y="150" w="150" h="30" />
<datatype>text</datatype>
<initialvalue />
<private />
<alwaysinit />
</stage>
<stage stageid="b70ee0cc-9209-4adb-aad0-419b406a6573" name="TDD Output Collection" type="Collection">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-195" y="30" w="150" h="30" />
<datatype>collection</datatype>
<private />
<alwaysinit />
</stage>
<stage stageid="098c2285-dea9-405a-b0e6-78f09e77809e" name="TDD Filtered Collection" type="LoopStart">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<loginhibit onsuccess="true" />
<display x="75" y="30" w="90" h="30" />
<onsuccess>2d1db40b-2e41-4261-98b6-33c0220ac7f9</onsuccess>
<groupid>3940ec89-fff0-4b88-acea-6d367a6359bc</groupid>
<looptype>ForEach</looptype>
<loopdata>TDD Output Collection</loopdata>
</stage>
<stage stageid="a244797a-99c5-4b9e-a098-732c1cd9aa05" name="TDD Filtered Collection" type="LoopEnd">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<loginhibit onsuccess="true" />
<display x="75" y="210" w="90" h="30" />
<onsuccess>025ccff3-f2ab-4d74-ac70-9638ac4bcdb8</onsuccess>
<groupid>3940ec89-fff0-4b88-acea-6d367a6359bc</groupid>
</stage>
<stage stageid="222eb42a-842e-4a42-b817-54fadb7cbc34" name="Is Date in Range" type="Decision">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="75" y="150" w="90" h="30" />
<decision expression="ToNumber(Trim([DateCode]))>ToNumber(Trim([GetToDate]))" />
<ontrue>51de23e3-222d-421e-a476-7e363965580c</ontrue>
<onfalse>72efbb07-76c0-4af9-aa82-b7032c7e144e</onfalse>
</stage>
<stage stageid="2d1db40b-2e41-4261-98b6-33c0220ac7f9" name="Get Range" type="MultipleCalculation">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<loginhibit onsuccess="true" />
<display x="75" y="90" w="90" h="30" />
<onsuccess>222eb42a-842e-4a42-b817-54fadb7cbc34</onsuccess>
<steps>
<calculation expression="Left([TDD Output Collection.Date Range], InStr([TDD Output Collection.Date Range],[MonthCode]))" stage="GetFromDate" />
<calculation expression="Replace(Right([TDD Output Collection.Date Range], InStr([TDD Output Collection.Date Range],"to")),"o","")" stage="GetToDate" />
<calculation expression="Left(Trim([GetFromDate]), 2)" stage="GetFromDate" />
<calculation expression="Left(Trim([GetToDate]), 2)" stage="GetToDate" />
</steps>
</stage>
<stage stageid="a1613be8-0f8f-4ca8-b64c-78b461b4f0f3" name="GetFromDate" type="Data">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-195" y="210" w="150" h="30" />
<datatype>text</datatype>
<initialvalue />
<private />
<alwaysinit />
</stage>
<stage stageid="48e42f95-a7a9-4bf2-bbf2-b9ea1ccff57c" name="GetToDate" type="Data">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-195" y="60" w="150" h="30" />
<datatype>text</datatype>
<initialvalue />
<private />
<alwaysinit />
</stage>
<stage stageid="51de23e3-222d-421e-a476-7e363965580c" name="Anchor1" type="Anchor">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<loginhibit onsuccess="true" />
<display x="180" y="150" w="10" h="10" />
<onsuccess>0ee1314c-deea-483e-998d-694c931bc9b5</onsuccess>
</stage>
<stage stageid="0ee1314c-deea-483e-998d-694c931bc9b5" name="Anchor2" type="Anchor">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<loginhibit onsuccess="true" />
<display x="180" y="210" w="10" h="10" />
<onsuccess>a244797a-99c5-4b9e-a098-732c1cd9aa05</onsuccess>
</stage>
<stage stageid="72efbb07-76c0-4af9-aa82-b7032c7e144e" name="Set TDD Code" type="Calculation">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<loginhibit onsuccess="true" />
<display x="-45" y="150" w="90" h="30" />
<onsuccess>025ccff3-f2ab-4d74-ac70-9638ac4bcdb8</onsuccess>
<calculation expression="[YearCode]&[TDD Output Collection.Code]" stage="TDD Code" />
</stage>
<stage stageid="facf7f67-80d4-4ffe-bcb6-592a82737e4a" name="TDD Code" type="Data">
<subsheetid>b01af1ed-09ce-4704-9760-750eefedaa81</subsheetid>
<display x="-195" y="90" w="150" h="30" />
<datatype>text</datatype>
<initialvalue />
<private />
<alwaysinit />
</stage>
</process>

--------------------------------

Best Regards,

Sayeed Bin Abdullah

Re: Duration calculation logic for Ten days period

sandeshtope — Mon, 16 Dec 2024 16:21:24 GMT

Thanks @SayeedBinAbdullah , It worked ! Thanks alot ! 🙂