Help

Copy date from one field to another without turning it into a string

Topic Labels: Dates & Timezones Formulas
163 3
cancel
Showing results for 
Search instead for 
Did you mean: 
hannahRFP
6 - Interface Innovator
6 - Interface Innovator

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!

3 Replies 3

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

Screenshot 2024-02-13 at 8.51.00 PM.png

Screenshot 2024-02-13 at 8.51.59 PM.png

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

 

hannahRFP
6 - Interface Innovator
6 - Interface Innovator

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
)

Screenshot 2024-02-14 at 12.39.43 PM.png
Link to base