Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Dates & Timezones Formulas
1435 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