Problem with Editing Dates

[#1]

Hello,

Im currently having and unkown problem.

When i try to use the function date(floor(timestamp#(Time, 'DD.MM.YYYY hh:mm')), 'DD/MM/YYYY'), it dosent work properly.

I dont know why, the dates that have a time earlier than 10:00, it dosnt work.´

Any one can help me finding out whats the problem?

I attached the example and the Data

thanks,

[Nicole-Smith · Accepted solution]

You have an extra space if the hour value is only one digit, so a simple replace() to replace where there are two spaces with just one space should work:

date(floor(timestamp#(replace(Time, '  ', ' '), 'DD.MM.YYYY hh:mm')), 'DD/MM/YYYY')

EDIT: Your fixed .qvw also attached.

[Anonymous]

Nicole, I suspect there is a simpler solution - replace hh with single h:

date(floor(timestamp#(Time, 'DD.MM.YYYY h:mm')), 'DD/MM/YYYY')

Can you try?

[Nicole-Smith]

I tried that out and it didn't work--I think because of the double space (see attached).

[Nicole-Smith]

But Mariana Orozco, I think it would be better to use the single h like Michael Solomovich has since you do have some single digit hours, but the replace is still needed:

date(floor(timestamp#(replace(Time, '  ', ' '), 'DD.MM.YYYY h:mm')), 'DD/MM/YYYY')

[Anonymous]

OK, thank you.  I though it was because of discrepancy of time format in excel and QV file ...

[#7]

thanks alot guys, really helpfull