Creating date fields in load editor

[davehutchinson]

Hi all.  I'm stuck with an issue of creating date fields in load editor. 

My original date field is [day] and in the format of 'DD/MM/YYYY hh:mm:ss'

From this I need to create:

1. a simplified date field of only DD/MM/YYYY i.e without the time

2. a week commencing date

3. Month.

I have created a table sheet to check how things are working and have managed to work out the following formulas that work fine.

1. Simplified date:

=Date#(Left([day],10),'DD/MM/YYYY')

2. Week Commencing:

=WeekStart(Date#(Left([day],10),'DD/MM/YYYY'))

3. Month: 

=Month(Date#(Left([day],10),'DD/MM/YYYY'))

All three of these formulas work fine.  however when I put the same script into the load editor no data is returned, and I can't work out where I'm going wrong.

Here's a screenshot of my load editor with the formulas in:

And here's a screenshot of my table showing the 3 fields that aren't working, then the 3 columns on the right, with the same formula which is working.... 😖🤯

I can't work out why the columns i've put in my load editor aren't working, yet they're working when you put them in the table....  

Can anyone shed any light on this?

Thanks 

[Muthukumar_77]

Hi,

Try this,

Date(Floor(Timestamp#([Day],'DD/MM/YYYY hh:mm:ss')),'DD/MM/YYYY')
Date(WeekStart(Floor(Timestamp#([Day],'DD/MM/YYYY hh:mm:ss'))),'DD/MM/YYYY')
Month(Floor(Timestamp#([Day],'DD/MM/YYYY hh:mm:ss')))

[davehutchinson]

So the same thing has happened.  The formulas work if I put them as columns in the table, but if I use the same formula in the data load editor the fields load but don't bring in any data.......

Is there anything that would prevent fields being created in the load editor maybe?

[Anil_Babu_Samineni]

FIFO, for #1, can you try this

=Date(Date#(day,'DD/MM/YYYY hh:mm:ss'))

Can you confirm how it looks like?

[maxgro · Accepted solution]

try with

Date(Floor(day)) 

[Muthukumar_77]

Can you share that excel file? I will check.

[davehutchinson]

@Anil_Babu_Samineni and @maxgro 

so i tried:

Date(Date#([day],'DD/MM/YYYY hh:mm:ss')) as day3,
Date(Floor(day)) as day4,

and the "day4" one worked, but day3 didn't.

So I'll use the "Date(Floor(day))" method going forward.

Cheers all!