Surreybloke
Board Regular
- Joined
- Apr 1, 2010
- Messages
- 159
- Office Version
- 365
- Platform
- Windows
The data that I'm working has been downloaded from Salesforce, and wouldn't normally be an issue, but for some reason this month, I am have problems. We have a close date field, start date field and a last stage change date field. Some of these fields will sometimes be blank. Where the month is January to September, it doesn't have a leading zero in the data due to having to use Salesforce in a United States date format, rather than for the United Kingdom where this would not happen.
I have a column that looks at the MAX (or most recent) date and automatically retrieves that from the date fields that come through in the raw data, as mentioned above. For some reason this month, the MAX formula is sometimes populating a date of 01/00/1900 even though not all the date fields are blank. This has never previously happened in the years of working on this data before.
I have found that by manually adding a zero in front of the month to the close date that it automatically replaces the 01/00/1900 with a valid date.
My question is whether there is a faster/automated way of being able to do this. Concatenation would be one way but would be quite a faff as not all rows have this problem, thereby making it harder to correct the affected rows. The same issue would arise by converting to a text format from the date format.
Many thanks in advance!
I have a column that looks at the MAX (or most recent) date and automatically retrieves that from the date fields that come through in the raw data, as mentioned above. For some reason this month, the MAX formula is sometimes populating a date of 01/00/1900 even though not all the date fields are blank. This has never previously happened in the years of working on this data before.
I have found that by manually adding a zero in front of the month to the close date that it automatically replaces the 01/00/1900 with a valid date.
My question is whether there is a faster/automated way of being able to do this. Concatenation would be one way but would be quite a faff as not all rows have this problem, thereby making it harder to correct the affected rows. The same issue would arise by converting to a text format from the date format.
Many thanks in advance!
