Formulae: Convert a time field (hh:mm) to an integer?

JamesDutton · July 2, 2012 9:44PM

I'm trying to create a formula that takes a time field (in the format hh:mm) and multiplies by a revenue figure it to create a billings model for people. However, in the current format I can't multiply by anything. Makes sense. So, my question is - is it possible to convert this field to an integer (i.e. convert hours & mins to mins) - e.g. 01:30 becomes 90. That 90 would be multiplied by their hourly rate - e.g. $100 = $900 billings.

indranildatta · July 3, 2012 7:55AM

Hi James,

I am assuming that you have two date fields here as you're essentially talking about a time duration.

It that's true, then please try the function DATEDIFF(Date1, Date2, "hour"). This will give you the difference in numbe, which you can then multiply by Rate.

I hope this helps.

Cheers!
Indranil

paola · July 3, 2012 10:14AM

Hi James,

Actually you are dealing with time value that represents duration, rather than point in time, which is why you cannot use date/time format for this field or related functions, such as DATEDIFF.
-You should use Field Organiser to format the duration field as Text,
-Expand operation to separate the Duration value in two, using ":" separator
- Add new Field Organiser to format/rename the two new fields (Hours/Minutes) as integer and to add Formula: ([Hours]*60+[Minutes])*[Rate]

Welcome!

Categories

Ideas Parade

Tagged