Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Duration calculation logic for Ten days period

sandeshtope
Level 5

‎09-12-24 04:17 AM

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 ?

0 Likes
6 REPLIES 6

Nandhakumar
Level 6

‎09-12-24 07:21 AM - edited ‎09-12-24 07:28 AM

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

SayeedBinAbdullah
Level 4

‎11-12-24 05:57 AM

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...
SayeedBinAbdullah_0-1733896287752.png

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

Senior Consultant - Automation Developer
Wonderbotz

sandeshtope
Level 5

a month ago

Hi @Nandhakumar  & @SayeedBinAbdullah  

Below is the TDD Logic 

Date Range TDD

01 Dec 2023 to 10 Dec 2023202335
11 Dec 2023 to 20 Dec 2023202336
21 Dec 2023 to 31 Dec 2023202401
01 Jan 2024 to 10 Jan 2024202402
11 Jan 2024 to 20 Jan 2024202403
21 Jan 2024 to 31 Jan 2024202404
01 Feb 2024 to 10 Feb 2024202405
11 Feb 2024 to 20 Feb 2024202406
21 Feb 2024 to 29 Feb 2024202407
01 Mar 2024 to 10 Mar 2024202408
11 Mar 2024 to 20 Mar 2024202409
21 Mar 2024 to 31 Mar 2024202410
01 Apr 2024 to 10 Apr 2024202411
11 Apr 2024 to 20 Apr 2024202412
21 Apr 2024 to 30 Apr 2024202413
01 May 2024 to 10 May 2024202414
11 May 2024 to 20 May 2024202415
21 May 2024 to 31 May 2024202416
01 Jun 2024 to 10 Jun 2024202417
11 Jun 2024 to 20 Jun 2024202418
21 Jun 2024 to 30 Jun 2024202419
01 Jul 2024 to 10 Jul 2024202420
11 Jul 2024 to 20 Jul 2024202421
21 Jul 2024 to 31 Jul 2024202422
01 Aug 2024 to 10 Aug 2024202423
11 Aug 2024 to 20 Aug 2024202424
21 Aug 2024 to 31 Aug 2024202425
01 Sep 2024 to 10 Sep 2024202426
11 Sep 2024 to 20 Sep 2024202427
21 Sep 2024 to 30 Sep 2024202428
01 Oct 2024 to 10 Oct 2024202429
11 Oct 2024 to 20 Oct 2024202430
21 Oct 2024 to 31 Oct 2024202431
01 Nov 2024 to 10 Nov 2024202432
11 Nov 2024 to 20 Nov 2024202433
21 Nov 2024 to 30 Nov 2024202434
01 Dec 2024 to 10 Dec 2024202435
11 Dec 2024 to 20 Dec 2024202436
21 Dec 2024 to 31 Dec 2024202501
01 Jan 2025 to 10 Jan 2025202502
11 Jan 2025 to 20 Jan 2025202503
21 Jan 2025 to 31 Jan 2025202504
0 Likes

sandeshtope
Level 5

a month ago

Hi @SayeedBinAbdullah  & @Nandhakumar 

Thanks for the reply ,

Below is the logic I am looking for 

sandeshtope_0-1733994935526.png

 

0 Likes

SayeedBinAbdullah
Level 4

a month ago

Hi  @sandeshtope 

Thanks for sharing more details about the problem,

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

Assumptions:

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

Note:

  1. 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.
  2. The given logic can still be fine tuned.
  3. Please copy the below code and paste it as a page in the process.

SayeedBinAbdullah_0-1734009135853.png

 

<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="&quot;[Date Range] like '%&quot;&amp;[MonthCode]&amp;&quot;%'&quot;" />
</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]),&quot;yyyy&quot;)&#xA;" stage="YearCode" />
<calculation expression="FormatDate(ToDate([Date to Search]),&quot;MMM&quot;)" stage="MonthCode" />
<calculation expression="FormatDate(ToDate([Date to Search]),&quot;dd&quot;)" 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]))&gt;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],&quot;to&quot;)),&quot;o&quot;,&quot;&quot;)" 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]&amp;[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

Senior Consultant - Automation Developer
Wonderbotz

sandeshtope
Level 5

4 weeks ago

Thanks @SayeedBinAbdullah  , It worked ! Thanks alot ! 🙂