I have a given time but need to subtract 20 minutes

In my table I have a date/time column that is Time only. I need to subtract 20 minutes from the that Time.

You can use a math column to subtract 0.0138888 from a datetime.

You should use 20/1440 to not loose decimals

1 Like

Date - 20/1440

2 Likes

I need the result to be a Date/time format. I am combining the PickDate and Time columns using a Template then Math column. I need an additional column with a time that is 20 minutes earlier. For example I need the time at 8:02 am and a time at 7:42 am. The date part will not change and the range for the original time is between 8:00am and 4:00pm each day.

Use a math column with Date - 20/1440.
Set Date to your date column. It will be a new column.

I don’t get any results.

Try to add a format datetime column and inject DateTime column. Choose medium date & time / no seconds. Then use that new column in the math column.

Is it still giving nothing?

The original data set lists the date and time seperately. I combine them with a template then convert the template into a date/time object using a math column (DateTime). It doesn’t seem to like doing any additional math against the DateTime column.

Format Date with the DateTime column doesn’t produce anything

The format you gave isn’t valid. Try to set D T

DateTime is already a date format. I have tried converting DateTime to an int using math column then subtracting different values but converting it back to a date produces inconsistent results.

Year(Date) * 100000000

  • Month(Date) * 1000000
  • Day(Date) * 10000
  • Hour(Date) * 100
  • Minute(Date)

Why do you have separate date and time columns instead of a single date column filled with a datetime picker? That way you would already have date and time in the same column without having to merge them which is a whole different challenge altogether.

The data comes from an excel spreadsheet. The time is assigned to the user. I join the date and time using a template then a math column to convert it to a date. I then relate this table to the user’s table.

I think that makes sense. I think I would do a couple things differently.

  • Instead of trying to combine the date and time with a template column, I would go straight to the math column to pull year/month/day from the date column and hour/minute from the time column.
  • Then I think you should be using the Date from Text column. Format date tries to convert a date too the specified format as opposed to from that format.

Another alternative would be to use the template column, and then go straight to the Date from Text column without doing any math.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.