Newbe's question

For HanDBase related topics that are not specific to one platform.

Newbe's question

Postby wladzinek » Mon Feb 02, 2015 5:02 pm

I am not proficient in databases at all, but I was able to create my database with the majority of features I needed. I am stuck with rounding up the time. Some jobs are less than one hour, so I need to round it up to one hour and some jobs exceed one hour. I have a field calculating the amount of time, but it returns real time eg. 10 minutes, 45 minutes or 1 hour and 20 minutes etc. I am looking for the formula to calculate either the first hour or real time.
wladzinek
 
Posts: 5
Joined: Mon Feb 02, 2015 2:55 pm

Re: Newbe's question

Postby curtterp » Tue Feb 03, 2015 9:59 pm

wladzinek wrote:I am not proficient in databases at all, but I was able to create my database with the majority of features I needed. I am stuck with rounding up the time. Some jobs are less than one hour, so I need to round it up to one hour and some jobs exceed one hour. I have a field calculating the amount of time, but it returns real time eg. 10 minutes, 45 minutes or 1 hour and 20 minutes etc. I am looking for the formula to calculate either the first hour or real time.


You are very close.... you only need a couple of more fields.

I am assuming that you have a field name called "Elapsed Time" which is your time calculation.

Here are the steps you will need to add
  • Add a calculated field. Call it something like "Middle". The calculation is "Elapsed Time + 0"
  • Add a conditional field. Call it something like "Roundup". Set the conditional formula like this, If "Middle" is less than No Field use value: 3600, Result is "No Field, use Value 3600" Else Result is "Middle"
  • Add a calculated field. Use the formula of "Roundup + 0". Set the result format In Time Format.

What we are doing here is taking your time calculation, putting it into an integer field by adding 0 to the elapsed time, checking for less than an hour with the conditional field and setting it to 1 hour if less than an hour, and using the actual time if over an hour. Then the last calculation field is putting the time back into a time format.

You can hide the elapsed time field, the first calculation field (Middle) and the conditional field (Roundup). Make sure that the second calculation field is viewable because that is your new elapsed time field.
Have a good day

Curt
I am not a DDHSoftware employee, just a long time HanDBase user.... from Palm to Windows Mobile to Android, to iOS. Thanks to DDH, the database files transferred to each platform without a problem.
curtterp
 
Posts: 679
Joined: Tue May 26, 2009 6:41 pm
Location: Northern IL

Re: Newbe's question

Postby curtterp » Tue Feb 03, 2015 10:20 pm

I am attaching a zip file here to that does the time calculations with sample data.


Here is an explanation of the fields in the database
  • Start Time - This is the starting time
  • End Time - This is the ending time
  • Elapsed Time - The time difference between Start Time and End Time (End time - Start Time)
  • Middle - The calculated field to convert time into an integer
  • RndUp - This is the conditional field that I used to check for less than 1 hour, setting to 1 hour, or using elapsed time if over an hour
  • ElapsedRoundedTime - this converts the conditional field back into the time format.

The only fields visible in the edit screen or the list screen is start time, End Time and ElapsedRoundedTime. The original Elapsed time, Middle, and Rndup Fields are hidden because they serve no purpose showing up in the fields.
Attachments
RoundTimeUp.PDB.zip
Database to show rounding time up
(1.58 KiB) Downloaded 89 times
Have a good day

Curt
I am not a DDHSoftware employee, just a long time HanDBase user.... from Palm to Windows Mobile to Android, to iOS. Thanks to DDH, the database files transferred to each platform without a problem.
curtterp
 
Posts: 679
Joined: Tue May 26, 2009 6:41 pm
Location: Northern IL

Re: Newbe's question

Postby wladzinek » Wed Feb 04, 2015 6:24 am

Thanks a lot! I did not expect it to be so simple.
Have a great day!
wladzinek
 
Posts: 5
Joined: Mon Feb 02, 2015 2:55 pm


Return to HanDBase General Discussion

Who is online

Users browsing this forum: Bing [Bot] and 1 guest