Skip to main content

Hi all, I have a base that date and timestamps new records. But since I had to import thousands of records from an old spreadsheet, it doesn’t correctly reflect the original date/time in the CREATED TIME field. So I created a field called DATE + TIME - MANUAL that is a date field that has the original submitted dates for the old records. All future records will get a submitted date/time in the DATE + TIME - AUTOMATIC field. In a third field called DATE + TIME SUBMITTED, I need to use a formula to say that, for a new record, if the DATE + TIME SUBMITTED - MANUAL field is blank, to use the data in the DATE + TIME SUBMITTED - AUTOMATIC field, but using this formula doesn’t bring in the data from the automatic field if the manual field is blank:

IF(NOT(BLANK({Date + Time Submitted - Manual})),
      {Date + Time Submitted - Manual},
      {Date + Time Submitted - Automatic})


Am I doing something incorrectly? Or is there a better way to achieve this? In the end, I just need to have a date field that has the original submitted dates and times of all the old records and the new submitted dates and times of all the new records.

 

Thanks!! 

The formula should be:

IF({Date + Time Submitted - Manual},
   {Date + Time Submitted - Manual},
   {Date + Time Submitted - Automatic}
)

If(field) works to analyze if the field is filled or empty.

That did it, thank you!!! One last question. My other 2 date fields use Pacific/Los Angeles time zone, but the formula field is using GMT. Would I use the DATETIME_PARSE function? And how would I add that to the above formula? Thanks!

You can format this new formula field just as you did with the other 2 date fields. It detects that the output of the formula is a Date, so when you open the formula field’s configuration you’ll be able to set the time zone in the “formatting” section.

 

AH! I totally overlooked the FORMATTING tab. Thank you again! Really appreciate the help.

Reply


Terms & Conditions