Help

It's about combining three integer field types into a date/object type

Topic Labels: Formulas
Solved
Jump to Solution
761 2
cancel
Showing results for 
Search instead for 
Did you mean: 
irvingjbeltran
4 - Data Explorer
4 - Data Explorer

I'm in a pickle and I can't figure out what to do even though I've read a lot of posts here and asked ChatGPT to help me over and over but nothing.

I've got three fields that are formulas but they're formatted into integer type. 

1. {Fecha limite de adeudo (dia)} and it's formula:

IF(
OR({Quincena 1}, {Quincena 2}),
IF({Quincena 1},
17,
1)
)
 
2. {Fecha limite de adeudo (mes)} and it's formula:
IF(
{Quincena 1},
MONTH(
TODAY()
)
,
IF(
{Quincena 2},
MONTH(
DATEADD(TODAY(), 1, 'month')
)
)
)
 
3. {Fecha limite de adeudo (ano) (number)} and it's formula:
VALUE({Fecha limite de adeudo (ano) (string)})
 
I tried combining them the following way but Airtable says "the result type is not a number or a date".
IF(
OR({Quincena 1}, {Quincena 2}),
DATETIME_PARSE(CONCATENATE({Fecha limite de adeudo (dia)},'-',{Fecha limite de adeudo (mes)},'-',{Fecha limite de adeudo (ano) (string)}), 'DD-MM-YYYY'),
""
)
 
I want this last field to be a date/object type and I don't know why it's not working because the three field types that are being used in this last field's formula are integers.
 
Could you please help me?
 
1 Solution

Accepted Solutions
AirOps
7 - App Architect
7 - App Architect

Hi @irvingjbeltran

The route of your issue is in your final calculation, it's also a very small mistake. In order to get the result you would like you need to remove the false condition from your formula (highlighted in red below). This is because if you keep the empty "", airtable assigns this columns result type as a string, since one of the possible results of your formula is technically an empty string. By removing that condition, you formula only executes when one of your Quincena fields is populated, rather than executing on all and assigning an empty string to those records where those fields are not populated. 

Current Formula: 

IF(
OR({Quincena 1}, {Quincena 2}),
DATETIME_PARSE(CONCATENATE({Fecha limite de adeudo (dia)},'-',{Fecha limite de adeudo (mes)},'-',{Fecha limite de adeudo (ano) (string)}), 'DD-MM-YYYY'),
""
)
 
New Formula: 
IF(
OR({Quincena 1}, {Quincena 2}),
DATETIME_PARSE(CONCATENATE({Fecha limite de adeudo (dia)},'-',{Fecha limite de adeudo (mes)},'-',{Fecha limite de adeudo (ano) (string)}), 'DD-MM-YYYY')
)

I hope this helps!

See Solution in Thread

2 Replies 2
AirOps
7 - App Architect
7 - App Architect

Hi @irvingjbeltran

The route of your issue is in your final calculation, it's also a very small mistake. In order to get the result you would like you need to remove the false condition from your formula (highlighted in red below). This is because if you keep the empty "", airtable assigns this columns result type as a string, since one of the possible results of your formula is technically an empty string. By removing that condition, you formula only executes when one of your Quincena fields is populated, rather than executing on all and assigning an empty string to those records where those fields are not populated. 

Current Formula: 

IF(
OR({Quincena 1}, {Quincena 2}),
DATETIME_PARSE(CONCATENATE({Fecha limite de adeudo (dia)},'-',{Fecha limite de adeudo (mes)},'-',{Fecha limite de adeudo (ano) (string)}), 'DD-MM-YYYY'),
""
)
 
New Formula: 
IF(
OR({Quincena 1}, {Quincena 2}),
DATETIME_PARSE(CONCATENATE({Fecha limite de adeudo (dia)},'-',{Fecha limite de adeudo (mes)},'-',{Fecha limite de adeudo (ano) (string)}), 'DD-MM-YYYY')
)

I hope this helps!

irvingjbeltran
4 - Data Explorer
4 - Data Explorer

I didn't know about that! Thank you very much!