# Convert text to number or date with formulas

**URL:** <https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375>\
**Category:** Ideas & Features\
**Tags:** formulas\
**Created:** [January 12, 2022, 11:02pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375 "2022-01-12T23:02:09Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![DioniGR27](https://avatars.discourse-cdn.com/v4/letter/d/e495f1/32.png) [@DioniGR27](https://community.fibery.io/u/DioniGR27)\
**Post date:** [January 12, 2022, 11:02pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/1 "2022-01-12T23:02:09Z")

</div>

Hi, I think Fibery should let users convert a text to a number or date, using formulas.

I have a Period type, which I named as ‘Q’ + Quarter + ‘-’ + Year:

I.E: Q1-2022, for referring to the First Quarter of 2022.

If I have this feature, I could fill their date range with automation or formulas, instead of manually.

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [January 13, 2022, 4:41pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/2 "2022-01-13T16:41:04Z")

</div>

A Formula Field can calculate a `DateTime` or `DateRange` from a string.

But I am not clear what you mean when you say “Let users convert” –  
Can you describe where this “Q1-2022” string exists, and where the corresponding Date/range would exist, and what would trigger that conversion?

---

<div class="post-metadata">

**Author:** ![DioniGR27](https://avatars.discourse-cdn.com/v4/letter/d/e495f1/32.png) [@DioniGR27](https://community.fibery.io/u/DioniGR27)\
**Post date:** [January 13, 2022, 5:48pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/3 "2022-01-13T17:48:05Z")

</div>

Not really, I have this on Fibery:  
 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/7/7bc15840b2635ef7eb06339d1f9c507a17eb2049.png)  
I want to configure an automation to calculate the DateRange from the name of the Period.  
This automation will be triggered when a Period is created.  
I don’t have a way to convert the year from a string to a number, in order to make the DateRange.  
A formula for Q1-2021 should be like this:

DateRange(Date(**Right(Name,4)**, 1, 1), Date(**Right(Name,4)**, 3, 31))

But it won’t work because **Right(Name,4)** is a string, and a number is expected.

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [January 13, 2022, 9:44pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/4 "2022-01-13T21:44:37Z")

</div>

Ah, I see – Fibery is missing a “parseInt” or “ToNumber” function in formulas 😢

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [January 13, 2022, 9:51pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/5 "2022-01-13T21:51:55Z")

</div>

There is currently no support for converting text to numbers unfortunately.  
But have you considered approaching the problem from a different angle: you could define two number fields (T and Y) and then the name can be generated using a formula, e.g.

`"T" + ToText(T) + "-" + ToText(Y)`

and you can generate the dates from these fields as well:

`DateRange(Date(Y,(3 * T) - 2,1),If(T = 4,Date(Y + 1,1,1) - Days(1),Date(Y,(3 * T) + 1,1) - Days(1)))`

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [January 13, 2022, 10:31pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/6 "2022-01-13T22:31:08Z")

</div>

This Rule-based approach seems to work - it automatically converts a Text field (named ‘String’ in the example) to an Number or Date, and stores the result in another field(s) of the appropriate type.

 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/1/12b8192445decffe3ae422266c69a7daa247cd32.png)

_ **Code:** _

```auto
const fibery = context.getService('fibery');
for (const entity of args.currentEntities) {
    const str = entity['String']; // input field name (text)
    await fibery.updateEntity(entity.type, entity.id, {
        'Number1': parseInt(str), // output field name and value
        'Number2': parseFloat(str), // output field name and value
        'Date1': new Date(str) // output field name and value
    });
}

```

 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/b/bb393746c40274bb19fc789af3731538bfe017a0.png)

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [January 13, 2022, 10:47pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/7 "2022-01-13T22:47:45Z")

</div>

Yes, thanks Matt.  
It is possible to use JS conversions in an automation.  
Good thinking!

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [January 13, 2022, 10:49pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/8 "2022-01-13T22:49:43Z")

</div>

Of course, the automation method needs the date range field to be a read/write field, which may be a disadvantage if you want to be sure that no-one will accidentally change the dates 😕

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [January 14, 2022, 2:50am UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/9 "2022-01-14T02:50:35Z")

</div>

@Chr1sG, are you certain that scripts can’t update “read only” fields? I remember reading that the API was not constrained, that this was only for the UI.

---

<div class="post-metadata">

**Author:** ![Sergey\_Truhtanov](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/sergey_truhtanov/32/13986_2.png) [@Sergey\_Truhtanov](https://community.fibery.io/u/Sergey_Truhtanov)\
**Post date:** [January 14, 2022, 10:08am UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/10 "2022-01-14T10:08:07Z")

</div>

That’s correct. You cannot modify readonly fields via scripts or API.

---

<div class="post-metadata">

**Author:** ![cannibalflea](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/cannibalflea/32/318_2.png) [@cannibalflea](https://community.fibery.io/u/cannibalflea)\
**Post date:** [February 18, 2022, 9:16am UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/11 "2022-02-18T09:16:25Z")

</div>

> [@Matt\_Blais](#):
>
> Ah, I see – Fibery is missing a “parseInt” or “ToNumber” function in formulas 😢

I also wanted to add my vote to adding a ToNumber function to the formulas. It is quite a useful thing to have and seems like a big omission from the current set.

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [February 18, 2022, 10:53pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/12 "2022-02-18T22:53:07Z")

</div>

> [@Sergey\_Truhtanov](#):
>
> You cannot modify readonly fields via scripts or API.

I must have been thinking of the API’s capability to **hide** specific entity fields from the UI.

---

<div class="post-metadata">

**Author:** ![interr0bangr](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/interr0bangr/32/8089_2.png) [@interr0bangr](https://community.fibery.io/u/interr0bangr)\
**Post date:** [November 9, 2023, 2:46pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/13 "2023-11-09T14:46:42Z")

</div>

Been coming up to this limitation quite frequently as I try to do automations around version names, KPIs, etc.

Is creating a “ToNumber” function that works essentially the same as “ToText” anywhere on the roadmap?

I’m not a developer so I struggle with the javascript workarounds for some of these things.

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [November 9, 2023, 3:14pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/14 "2023-11-09T15:14:35Z")

</div>

Here’s a workaround - The following Formula will convert the `Name` text field to an **Integer only** (NOT floating point) up to 9 digits (but you could add more).

If first “If” sets the value returned (-9999) if any non-digit characters are found in the `Name` field.

```auto
If( MatchRegex(Name,"[^0-9]"), -9999,
  If( Length(Name)<1, 0, (Find("0123456789", Middle(Name, Length(Name)-0, 1)) - 1) + 10 *
  If( Length(Name)<2, 0, (Find("0123456789", Middle(Name, Length(Name)-1, 1)) - 1) + 10 *
  If( Length(Name)<3, 0, (Find("0123456789", Middle(Name, Length(Name)-2, 1)) - 1) + 10 *
  If( Length(Name)<4, 0, (Find("0123456789", Middle(Name, Length(Name)-3, 1)) - 1) + 10 *
  If( Length(Name)<5, 0, (Find("0123456789", Middle(Name, Length(Name)-4, 1)) - 1) + 10 *
  If( Length(Name)<6, 0, (Find("0123456789", Middle(Name, Length(Name)-5, 1)) - 1) + 10 *
  If( Length(Name)<7, 0, (Find("0123456789", Middle(Name, Length(Name)-6, 1)) - 1) + 10 *
  If( Length(Name)<8, 0, (Find("0123456789", Middle(Name, Length(Name)-7, 1)) - 1) + 10 *
  If( Length(Name)<9, 0, (Find("0123456789", Middle(Name, Length(Name)-8, 1)) - 1)
))))))))))

```

This could be further tweaked to ignore specific non-numeric characters like “.” and “-”, by adding `RegexReplace()` to _each line_ – e.g:  
`... (Find("0123456789", RegexReplace(Name, "[.,/-]", ""), Length( ...`

That would only be useful if each numeric sub-field is fixed-length (though additional bewildering complexity could possibly solve some of that 🤯 ).

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [November 9, 2023, 3:46pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/15 "2023-11-09T15:46:42Z")

</div>

Nice trick 😉

---

<div class="post-metadata">

**Author:** ![interr0bangr](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/interr0bangr/32/8089_2.png) [@interr0bangr](https://community.fibery.io/u/interr0bangr)\
**Post date:** [November 17, 2023, 2:19pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/16 "2023-11-17T14:19:38Z")

</div>

> [@Matt\_Blais](#):
>
> ```auto
> If( MatchRegex(Name,"[^0-9]"), -9999,
> If( Length(Name)<1, 0, (Find("0123456789", Middle(Name, Length(Name)-0, 1)) - 1) + 10 *
> If( Length(Name)<2, 0, (Find("0123456789", Middle(Name, Length(Name)-1, 1)) - 1) + 10 *
> If( Length(Name)<3, 0, (Find("0123456789", Middle(Name, Length(Name)-2, 1)) - 1) + 10 *
> If( Length(Name)<4, 0, (Find("0123456789", Middle(Name, Length(Name)-3, 1)) - 1) + 10 *
> If( Length(Name)<5, 0, (Find("0123456789", Middle(Name, Length(Name)-4, 1)) - 1) + 10 *
> If( Length(Name)<6, 0, (Find("0123456789", Middle(Name, Length(Name)-5, 1)) - 1) + 10 *
> If( Length(Name)<7, 0, (Find("0123456789", Middle(Name, Length(Name)-6, 1)) - 1) + 10 *
> If( Length(Name)<8, 0, (Find("0123456789", Middle(Name, Length(Name)-7, 1)) - 1) + 10 *
> If( Length(Name)<9, 0, (Find("0123456789", Middle(Name, Length(Name)-8, 1)) - 1) + 10
> ))))))))))
> 
> ```

Thanks @Matt_Blais, unfortunately I need to set a decimal number value!

@Chr1sG @mdubakov, is it not as easy as it would seem to just reverse the ToText function into a “ToNumber” function? I know a string can be anything and a number has a much smaller amounts of valid characters, but the naive PO in me thinks this would be a low effort/high value feature.

---

<div class="post-metadata">

**Author:** ![Matt\_Blais](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/matt_blais/32/1464_2.png) [@Matt\_Blais](https://community.fibery.io/u/Matt_Blais)\
**Post date:** [November 17, 2023, 3:10pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/17 "2023-11-17T15:10:34Z")

</div>

@interr0bangr  
This Rule will work to convert a text field to a floating point value. Note that there is no more Formula field - there is a Text field (input) and a Numeric field (output) that is updated by the script when the input Text field changes.

Fibery numeric fields are limited to max 8 digits to the right of the decimal.

 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/5/5c56c3671bf0545a223c0450e42247ff3aa19d8e.png)

```auto
const INPUT_TEXT_FIELD = 'NumText'
const OUTPUT_NUMERIC_FIELD = 'Num'
const fibery = context.getService('fibery');
for (const entity of args.currentEntities) {
    const num = parseFloat( entity[INPUT_TEXT_FIELD])
    await fibery.updateEntity(entity.type, entity.id, {
        [OUTPUT_NUMERIC_FIELD]: num
    })
}

```

---

<div class="post-metadata">

**Author:** ![SantiagoAGR](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/santiagoagr/32/8172_2.png) [@SantiagoAGR](https://community.fibery.io/u/SantiagoAGR)\
**Post date:** [December 15, 2023, 12:14pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/18 "2023-12-15T12:14:19Z")

</div>

Here’s a solution to converting Text to Float (decimals)

Firstly, thanks to @Matt_Blais for his awesome solution to convert Text to Integer! From now on, I will call his function “MattFunction” because I will explain the solution for anyone interested.

**In essence, the solution is:**  
InputText  
→ Convert the integer part to Integer (Int\_int) and convert the Decimal part to Integer (Dec\_int) using the MattFunction

→ Convert Dec\_int to float (Dec\_dec) by diving it by a multiple of 10 → Dec\_dec = (Dec\_int/Power(10, Length(Dec\_int))

→ Create the final float using Int\_int + Dec\_dec

**Example**  
Input Text = 123.678  
Int\_int = 123  
Dec\_int = 678  
Dec\_dec = 678 / 10^3 = 0.678  
Float = 123 + 0.678

- How do we get Int\_int and Dec\_int? → Using the Left, Right, Find, and Length Functions.
- Does it work with regular Integers and/or regular decimals? → Yes if we add some conditionals

 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/f/f912873e402a2a7931b2fa05d7bc30130d683af3.png)

Easiest way to replicate it is to use helper Formula Columns like in the screenshot above but if we wrap all of them in a single one we get the following **monster formula**. If you want to use it I guess you need to use a text editor to Find and Replace the “InputText” with the name of your target Column in Fibery.

```auto
If( MatchRegex(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)),"[^0-9]"), -9999,
  If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<1, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-0, 1)) - 1) + 10 *
  If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<2, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-1, 1)) - 1) + 10 *
  If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<3, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-2, 1)) - 1) + 10 *
  If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<4, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-3, 1)) - 1) + 10 *
  If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<5, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-4, 1)) - 1) + 10 *
  If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<6, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-5, 1)) - 1) + 10 *
  If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<7, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-6, 1)) - 1) + 10 *
  If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<8, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-7, 1)) - 1) + 10 *
  If( Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))<9, 0, (Find("0123456789", Middle(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)), Length(Left(InputText, If(Find(InputText, ".") = 0, Length(InputText), Find(InputText, ".") - 1)))-8, 1)) - 1) + 10
)))))))))) 
+
If( MatchRegex(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))),"[^0-9]"), -9999,
  If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<1, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-0, 1)) - 1) + 10 *
  If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<2, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-1, 1)) - 1) + 10 *
  If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<3, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-2, 1)) - 1) + 10 *
  If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<4, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-3, 1)) - 1) + 10 *
  If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<5, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-4, 1)) - 1) + 10 *
  If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<6, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-5, 1)) - 1) + 10 *
  If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<7, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-6, 1)) - 1) + 10 *
  If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<8, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-7, 1)) - 1) + 10 *
  If( Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))<9, 0, (Find("0123456789", Middle(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))), Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))-8, 1)) - 1) + 10
)))))))))) 
/
Power(10,Length(If(Find(InputText, ".") = 0, "0", Right(InputText, Length(InputText) - Find(InputText, "."))))) 

```

Yes, the formula is unreadable, but it works with native Fibery Formulas. It is a good temporary solution until we get the “ToNumber” function or the ability to have custom functions or “Lambdas” or at least the “Lets” function.

---

<div class="post-metadata">

**Author:** ![interr0bangr](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/interr0bangr/32/8089_2.png) [@interr0bangr](https://community.fibery.io/u/interr0bangr)\
**Post date:** [December 21, 2023, 4:11am UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/19 "2023-12-21T04:11:46Z")

</div>

Thank you!

---

<div class="post-metadata">

**Author:** ![myg\_ge](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/myg_ge/32/12457_2.png) [@myg\_ge](https://community.fibery.io/u/myg_ge)\
**Post date:** [October 28, 2025, 5:59pm UTC](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375/20 "2025-10-28T17:59:56Z")

</div>

Self-explanatory. Especially useful for folks without Scripting on their plans.

[Next page](https://community.fibery.io/t/convert-text-to-number-or-date-with-formulas/2375.md?page=2)