Using Today() formula and offset by day / hours issues

1

I’m trying to get ‘Value for new rows’ in a date column to be just the day with no time element. I’m not in Pacific Time so I have to add an adjustment, but I have a few issues with that approach.

I know that you’re working on providing Time Zone support, but in the meantime I’m wondering if there’s a way to make this work.

If I add the formula Now() + hours(21) in ‘value for new rows’, it works as expected, except that it also records the time, which I do not want =3/11/2019 12:13:33 PM I just want the day.

So I tried using the formula Today() + hours(21) which didn’t work =3/10/2019 9:00 PM

I also tried Today + days(0.875) which didn’t work and it still added the time element =3/10/2019 9:00 PM

I don’t think using the formula Today() + days(1) =3/11/2019 is accurate because, even though it doesn’t add the time, it adds 24 hours, not 21.

I find this annoying because when I pull data from the affected column, I don’t want to have a time, just the correct date.

PS I just realised that some daylight saving adjustment must have happened recently in Pacific Time, because I am now 20 hours away…

2

Set the column format to date and try this formula:

slice(totext(Today() +Hours(20)),1,10)

3

Hi Bianca, thank you for your suggestion.

I tried it, and it doesn’t work. I don’t get today’s date, but still yesterday’s. Also when it’s converted to text it doesn’t respect the Date Format I chose which is dd/mm/yyyy. It changes it to default which is mm/dd/yyyy. I’m also I’m using Gantt Chart view, and it doesn’t work if the date is converted to text. I get the error “Invalid Date”.

My column is already set to Date, that’s why it’s annoying that adding hours also adds the time element. (It doesn’t display in table view, but it appears in calendar view.)

Thanks for your help anyway.

4

Kind of a pain, but I think you can do it with 2 hidden columns.

First column hidden (Pacific Date Time) - Set as date with the formula

Now()

Second column hidden (Local Date Time) - set as date with the formula

slice(ToText(thisRow.[Pacific Date Time]+hours(20)),1,10)

Third Column (Local Date) - set as date with the formula

ToDate(thisRow.[Local Date Time])

5

Not sure I follow. If you format column as Date it should show date only. Date and Time, on the other hand, also records time.

6

It shows Date in table view, but it adds a time element to the data that is contained in each cell. Try to create calendar views of tables that have Date only columns and you’ll see what I mean.

7

Oh I see. It appears time is intrinsic to the object… The closest I can think of would be forcing time to 12AM by using DateTimeTruncate(Now()+Hours(21),"day").