Add Number to DateTime

[Simon1]

I'm trying to add a minutes as a Number to DateTime.

On the app the result shows the same as the Start Time.

When I test the expression the expression result shows the same as the start time, but the column for the result, Target End Time is calculating the correct DateTime with the added minutes

Calculation

[Start Date & Time]+([Target Time Make (mins)]/1440)

Thanks

[Suvrutt_Gurjar]

What is the maximum time in minutes that can be added? Will it be always less than 60 minutes or it can be 60 minutes or more?

[Simon1]

it can be more than 60 minutes

[Suvrutt_Gurjar]

Thank you. Can it be more than 24 hours as well?

[Simon1]

it wouldn't be more than 24 hours

[TeeSee1 · Accepted solution]

Hi 

This may not be exactly what you are looking for but I did some experiment because the documentation was not very clear to me and I was also curious how time calculations worked.

Apparently when you add an integer to a DateTime value, it is taken as the number of Days. So if you divide you Mins column by 1440, it is less than one day.

If you want to add minutes to a DateTime you have to add a Duration.

I tried to construct a Duration by concatenating strings "000:" & [Mins] & ":00" but this did not work.

So as an interim step, I created a VC with the above expression of type Duration and added this VC to my start col.

This seems to work very good.

You may want to do your own experimentation.

[Simon1]

That works. Thanks

[SkrOYC]

Take a look at this post by @Joseph_Seddik 

https://www.googlecloudcommunity.com/gc/Tips-Tricks/The-notions-of-TIME-and-DURATION-and-how-to-use-...

[TeeSee1]

I totally missed this!! Thanks!

[TeeSee1]

@SkrOYC 

I went through the document but did not see how to construct a duration dynamically.

Do you know a way to construct a Duration dynamically without my "workaround"?

[Suvrutt_Gurjar]

Could you evaluate making the column [Target Time Make (mins)] as time type?

Then you could add whatever minutes ( and hours) you want to add through this time type column with an expression as below

[Start Date & Time]+([Target Time Make (mins)]-"00:00:00")

The screenshot of form below shows how it looks 

The below image shows the columns type  setup

The expression in the column [ToDateTtime] is 

[FromDateTime]+([AddMinutes]-"00:00:00")

Here [AddMinutes]-"00:00:00" basically converts the tim to duration. This is required because one can add duration to time but not time to time.  

[Joseph_Seddik]

Wouldn't it be "000:00:00"? with three leading zeros? 

[Simon1]

I need the time as a number for other calculations later, which are google sheet calculations.

So I'd need one as duration and one as a number, whichever way I convert. 

[Suvrutt_Gurjar]

You can easily get time as a number from duration by using

https://help.appsheet.com/en/articles/3543770-totalminutes or 

https://help.appsheet.com/en/articles/3543771-totalhours  or

https://help.appsheet.com/en/articles/3543829-totalseconds as the need may be.

[Simon1]

That's useful. Thanks.

[SkrOYC]

As well as @Suvrutt_Gurjar's solution of replacing the number with Time (or Duration), I strongly suggest to not use any kind of VC for your calculations. The less VC, the better

[Suvrutt_Gurjar]

---

---

Wouldn't it be "000:00:00"? with three leading zeros? 

@Joseph_Seddik 

I believe that is not required because Time -Time=Duration 

[Joseph_Seddik]

Right, I didn't read well, I thought you were trying the otherway round, to calculate time. Thank you!