Need help with date formula

Question

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!!

Best answer by mtrebinonixon

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.

mtrebinonixon · Accepted Answer

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.

Chorchie · Answer

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 theDATETIME_PARSE function? And how would I add that to the above formula? Thanks!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded