10 Replies Latest reply on Dec 15, 2012 11:41 AM by brian.curran

# VLookUp a value in a range of data in a Table

### Title

VLookUp a value in a range of data in a Table

### Post

In MS Access, I had  a table that enable me to round up chargeable time to the nearest quarter of an hour. The minimum charge was 1 hour and this incremented in 15 minute chunks of time.

MinutesFrom | MinutesTo | HoursToCharge
1                         60                 1
61                       75                1.25
76                       90                 1.5
91                       105               1.75
Etc.

What is the best way to achieve the above in FM, is there a better way?

Thanks
Brian.

• ###### 1. Re: VLookUp a value in a range of data in a Table

Try using this function, where you send the minutes as a parameter:

Let ( [ m = minutes ] ;

If ( M < 61 ; 1 ; Ceiling ( m / 15 ) / 4 )

)

• ###### 2. Re: VLookUp a value in a range of data in a Table

Thanks William, I got it to work but only with a couple of extra fields. I returned the "minutes' as seconds in one field then /60 in another field to get the hours billed in another!

Is there a way to write:
DepartedAt - ArrivedAt /60

Thanks
Brian.

• ###### 3. Re: VLookUp a value in a range of data in a Table

Let ( [ m = ( DepartedAt - ArrivedAt ) / 60 ] ;

If ( M < 61 ; 1 ; Ceiling ( m / 15 ) / 4 )

)

• ###### 4. Re: VLookUp a value in a range of data in a Table

Even better, thanks Phil :)

• ###### 5. Re: VLookUp a value in a range of data in a Table

Another hint, if there is even the slightest chance that the interval of time from ArrivedAT to DepartedAT crosses the midnight hour, use TimeStamp fields in stead of time fields so that you record date and time in each. The computed interval that you get when you subtract them will still be in seconds, but now you do not have to worry about subtracting 1Am of the next day from 5 pm of the previous day.

• ###### 6. Re: VLookUp a value in a range of data in a Table

Yes, that happens all the time, can you confirm the following to ensure I understand:

_cCalledAt = CalledDate + CalledTime (type TimeStamp)
_cArrivedAt = ArrivedDate + ArrivedTime (type TimeStamp)
_cDepartedAt = DepartedDate + DepartedTime (type TimeStamp)

_cTimeToArrive = _cArrivedAt - _cCalledAt (type TimeStamp)
_cTimeOnSite = _cDepartedAt - _cArrivedAt (type TimeStamp)
_cTimeStartToFinish = _cDepartedAt - _cCalledAt (type TimeStamp)

When I set everything to TimeStamp, the result reads 01/01/0001 00:30:00
Does this just mean it's a 30 minute duration in the same day?

• ###### 7. Re: VLookUp a value in a range of data in a Table

You are adding apples and oranges here. Dates store a number that represent the number of days from 12/31/0000 to the date shown in the field. Time fields store the number of seconds since midnight and timestamp fields store the number of seconds since 12/31/0000

GetAsTimeStamp ( DateField & " " & TimeField )

or enter your dates and times into timestamp fields from the beginning

• ###### 8. Re: VLookUp a value in a range of data in a Table

Thanks Phil, I've used your 'GetAsTimeStamp' method to merge the Dates and Times to replace the first three Calcs shown above.

I've left the bottom 3 Calcs as they are but changed the type back to Time format. The final field to work out the number of billable hours has been set to type Number, which works great with results such as 1, 1.25, 1.5 etc.

Some of my users have difficulties entering checkbox data so asking them to enter a date and a time in the same field may be too much ;) Also not sure if this could be achieved with the scrolling Date and Time pop up windows on IOS devices.

Thanks again...

• ###### 9. Re: VLookUp a value in a range of data in a Table

In some cases, a convenient way to enter date, time or timestamp data is to provide a button where clicking/tapping it enters the current date/time from the computer's system clock.

• ###### 10. Re: VLookUp a value in a range of data in a Table

That may come in useful, thanks...