How to get just the date in ETL on data reader
Hi,
I'm trying to find a difference between a date when and item is released and today on my data reader. I have the release date pulling from an API source. I'm using the =today() formula to return a day on an ETL but it's pulling back the date and time stamp like this:
Then when I subtract the two I get a really off number (the negative number at the bottom here.
How do I get the =today() to just equal to a date, no time?
Accepted Answer
-
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 ;-)
0
Answers
-
Hi @John Hughes ,
You could try either of the following:-
=TEXT(TODAY(),"dd/MM/yyyy")
OR
=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.
0 -
Hi @John Hughes ,
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,
Helmut0 -
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.
0 -
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.
0 -
Hi @John Hughes ,
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,
Helmut0 -
@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.
0 -
please change D4 to date(D4), as well…
0 -
it might even be that you have to save date(D4) in a helper cell, first
0 -
and one more thing @John Hughes , the higher date needs to be the second one ;-)
0 -
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 "/".
0 -
Hi @John Hughes ,
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,
Helmut0 -
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!
0 -
yes, @John Hughes , you're misssing one opening bracket for the last today() in day(today())
0 -
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 ;-)
0 -
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.
0 -
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?
0 -
for maintenance reasons, it can be better to split up as much as you can but the number of helper fields is limited.
0 -
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.
0 -
OK, but I'm still getting #Value! when trying to convert D4 to date format.
0 -
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,
Helmut0 -
This did it! I had to take out the year, month, and day out of the formula and it put it into the date format.
0
