Skip to main content

What can I use in a formula field to copy the date from one field to another without changing it into a text string?

For context, I have install dates and site visit dates in the same table, and the dates are in the same column for 'start' and 'end'. I am setting up 4 more fields (Site visit start, site visit end, install start, install end) that will remain blank if the date type does not match. However, I can't find any way to keep the date as a number/ date format and not a text string? 

I basically just want to copy a date (unchanged, not adding or subtracting days or I could use DATEADD), but the only one I can find is DATETIME_FORMAT but this changes it into a string. 

Can anyone help? 

I understand I could use automations to copy the date, but I'm trying to limit my use of those because I am nearing the limit of automations per base! So a formula would be much better if possible. 

Code I'm currently using is below:

IF(

{Date Type} = "Install",



IF(

{End} != 0,

DATETIME_FORMAT({End}, 'D/MM/YYYY'),



IF(

{End} = 0,

""

)

), ""

)

Thanks so much in advance!

A formula field that just references a 'Date' type field outputs a date like so:



Which...I think is what you're trying to do?  Sorry if I've misunderstood!

 

Hi @TheTimeSavingCo,

Thank you for your reply! I've been writing some complex formula and somehow forgot/ overlooked this really simple thing! 

I want it to be blank if the date type isn't 'install', so I have used a BLANK() field for this instead of "" so that it remains a date, not a string. I also just used the field {End} instead of using any DATE formatting! I think I forgot you can just reference the field instead of using a function before it 😅 Oops

New formula that keeps it a date:

IF(

{Date Type} = "Install",



IF(

{End} != 0,

{End},



IF(

{End} = 0,

BLANK()

)

)

)

I could have used SWITCH instead of nested IF statements, but as there are only 2 variables (install or site visit) I will leave as is for now. 

Thanks for helping me think about it in a different way! 

 

Hi @TheTimeSavingCo,

Thank you for your reply! I've been writing some complex formula and somehow forgot/ overlooked this really simple thing! 

I want it to be blank if the date type isn't 'install', so I have used a BLANK() field for this instead of "" so that it remains a date, not a string. I also just used the field {End} instead of using any DATE formatting! I think I forgot you can just reference the field instead of using a function before it 😅 Oops

New formula that keeps it a date:

IF(

{Date Type} = "Install",



IF(

{End} != 0,

{End},



IF(

{End} = 0,

BLANK()

)

)

)

I could have used SWITCH instead of nested IF statements, but as there are only 2 variables (install or site visit) I will leave as is for now. 

Thanks for helping me think about it in a different way! 

 


Hmm, perhaps you could try:

IF(

AND(

{Date Type} = "Install",

End

),

End

)


Link to base

 

Reply


Terms & Conditions