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

2 weeks ago

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
5 REPLIES 5

Nandhakumar
Level 6

2 weeks ago - last edited 2 weeks ago

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

2 weeks ago

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

2 weeks 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

2 weeks 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

a week ago

Thanks @SayeedBinAbdullah  , It worked ! Thanks alot ! 🙂