How to get just the date in ETL on data reader

that's because the date function doesn't recognize 20241117 as a date and therefore can't derive year, month, and day from that…

you need to explicitly put it that way ;-)

All comments

Samson Sunny

You could try either of the following:-

=TEXT(TODAY(),"dd/MM/yyyy")

=TEXT(NOW(),"dd/MM/yyyy")

Note:- make sure to have the month as "MM" and not as "mm" as "mm" would return the minute value.

whenever you're dealing with a date in the form DD/MM/YYYY hh:mm:ss (timestamp included) you can use the datedif(a,b,"D") function to give you the difference in days. So if we're calling the loaded date <input_date> it would look like this

<difference>=datedif(date(today()),<input_date>,"D")

Best,
Helmut

https://community.board.com/discussion/comment/19030#Comment_19030

I tried this but it gave me a formula error when subtracting. I think it's because it put it in text and the other date field is in date format.

https://community.board.com/discussion/comment/19031#Comment_19031

When I put this in, and try to switch back from showing formulas, it tells me the datedif formula is not valid.

Here's what it looks like. I'm trying to take the date in row 4 and subtract it from the current date.

yeah, I think you'd need =datedif(date(today()),date(D4),"D")

Another hint: you're populating a description - is that correct? And you obviously do have an opening bracket too much (that's what the error is resulting from)

Best,
Helmut

@Helmut Heimann if I remove that opening bracket, I still get the same error.

I'm using the description field just as a dummy field, and not actually writing anything into it. I'll use it to give me a number of days and then reference that to mark a product in a separate field.

https://community.board.com/discussion/comment/19036#Comment_19036

please change D4 to date(D4), as well…

it might even be that you have to save date(D4) in a helper cell, first

and one more thing @John Hughes , the higher date needs to be the second one ;-)

I switched the higher date, today, to the second and put the other one first, but it's still saying the formula is invalid.

What format should the first date be in when reading it into Board? I was always under the impression it needed to be yyyymmdd with no "/".

I'm sorry I missed that, but the date function needs more than one argument, it needs to be something like this

=date(year(d4),month(d4),day(d4)), the same for "today()".

=date(year(today()),month(today()),day(today())) - will deliver 11/18/2024 00:00:00

Then it should work, I apologize for the inconvenience.

Best,
Helmut

I'm still getting an invalid formula for he Today date.

When I try the same thing with the data in D4, which is in yyyymmdd, it doesn't give an error, but displays as #VALUE!

https://community.board.com/discussion/comment/19042#Comment_19042

yes, @John Hughes , you're misssing one opening bracket for the last today() in day(today())

and for D4, please use =date(year(d4),month(d4),day(d4)), or even better =date(left(d4,4),mid(d4,4,2),right(d4,2))

the datedif() function needs the dates in date-format ;-)

I think the issue is with the other date I'm trying to capture. When I try anything with it, I get a #VALUE! error, even though it's being displayed in the preferred date method. It also works in another data reader I have as I used that same date, field, and formula to flag a cube on a specific date with that SKU's born on date. I'm not sure why I can't subtract it from a Today() date.

https://community.board.com/discussion/comment/19044#Comment_19044

Could I use two helper fields to put them in date formats, and then use the datedif formula, or does all that need to be in the datedif formula?

https://community.board.com/discussion/comment/19046#Comment_19046

for maintenance reasons, it can be better to split up as much as you can but the number of helper fields is limited.

https://community.board.com/discussion/comment/19045#Comment_19045

the today() date is in date format, the standard YYYYMMDD is not in date format, that's why you can only subtract them if they are in the same format—and that's the reason for the error message.

So, best would be to calculate the dates in date-format each in helper cells and then calculate the datedif() using those helper cells.

https://community.board.com/discussion/comment/19048#Comment_19048

OK, but I'm still getting #Value! when trying to convert D4 to date format.

Hi @John Hughes,

can you please put the parts in the second date-function like this

let's assume D4 = 20241117 (yesterday)

=date(left(d4,4),mid(d4,4,2),right(d4,2)) | meaning =date(year(<yesterday>),month(<yesterday>),day(<yesterday>))

Best,
Helmut

that's because the date function doesn't recognize 20241117 as a date and therefore can't derive year, month, and day from that…

you need to explicitly put it that way ;-)