Choosing the correct number formats for a weird table with time involved (1 Viewer)

I have a table which needs the following columns with the following formats (the formatting how I want it to appear is in the brackets):

Delay Hours (simple 2 digit format e.g 00, 01, 02, 15, 25 from 00 to 99 or 48)
Delay Mins (simple 2 digit format e.g 00, 01, 02, 15, 25 from 00 to 59)
Time (simple [Delay Hours] : [Delay Mins] format so it appears like 01:52)

I have a weird requirement that I need all 3 columns to exist as I have something which bolts onto the database which doesn't like the : in the time column.

How would I go about setting up the formats of the columns in order to get them to export correctly? The issue I am having at the moment is that I have been concatenating the Delay Hours and Mins as short text to create the Time field as I am wanting the hours and minutes to come out in a 2 digit format rather than something like 1:5 (which should be coming out as 01:05).

Ideally I would have Time be the thing collected by my form so the user would input for example 05:35 and then the Delay Hours and Delay Mins columns would be calculated from this.


Any help would be appreciated and let me know if you need me to explain anything extra.

In order to preserve the leading 0 these fields will need to be text fields. Is that OK?
Where is the time coming from?

"Bolts onto the database" is kind of ambiguous. Does this thing actually try to access your tables? Or do you have control over what you present?

If those items are derived from a time field, you probably cannot do this formatting directly from a table without some extra work beforehand. But if you could make a query with those values, you might be able to do what you want. You can export queries easily enough.

Before we can answer this completely, though... how big can the "Hours" field get? If you can delay more than 23 hours then this time field is going to be a mess to compute as you have to then start dinking around with Date variables a bit more intimately.

Date fields are kind of fun in that they are like numbers on a time-line. They are the number of days and fractions thereof, in DOUBLE format internally, since the reference date. So hours and minutes and such are fractions, days are the integer part. The difference between two date fields is the elapsed time between the two times, again in the same format.

If the time you are storing is actually the difference between two other time fields, then the number of hours is

CLng( difference * 24 )

and the number of minutes is

( CLng( difference * 1440 ) MOD 60

That time field however, has the problem that if you can exceed 24 hours of delay, a standard formatting function won't work for the hours part. If you CANNOT exceed 24 hours, the string is just

Format( difference, "hh:nn" )

Note also that if the time you are storing comes from another source, you need to specify the source for us.

@RuralGuy Unfortunately I am needing them to be spat out as a number for when they are exported into Excel. I am trying to minimise the formatting required by the user so I have quite a few lines of code to format the exports too.

I have managed to get it looking how I want it to in my table by using the following properties.

DelayHours:
Calculated field
Using the Expression Hour([DelayTime])
Result Type Integer
Format 00

DelayMins:
Calculated field
Using the Expression Minute([DelayTime])
Result Type
Integer
Format 00

DelayTime:
Date/Time
Format Short Time
Input Mask 00:00;0;_

When I export these columns though, the DelayTime field comes out as a date+time format e.g 00/01/1900 00:35:00 when in the database table it was appearing as 00:35. I get that times are saves as hours, minutes and seconds from the very beginning of 1900, but how do I go about rearranging the format that gets exported from Excel as hh:mm? I get that I can work around this by changing the format of the cells in the export in Excel, but I am trying to minimise the amount of clicks the user must do to provide a number of exports/reports so getting this sorted would be awesome.

@The_Doc_Man The DelayTime field that I have in my main table, is just a simple short time in the format hh:mm. It has no relevance to other fields and is just a characteristic of the Delays which I am collecting data for. So when the user enters 05:26, he is saying the delay lasted 5 hours and 26 minutes.

Sorry if I sound patronising, I just use a lot of words when I am trying to explain my issue because I often struggle to get what information people need to help with my issues.

I have solved this issue by adding this line of code into my formatting bit of VBA:



Is there a more efficient way of doing this though which tackles the problem at source?

Although I have managed to get this working, it will only work for values up to 23:59 due to the date field parameter being a short time. How do I go about making it so that I can have something like 36:34 for 36 hours and 34 minutes?

The most common method I have seen knocking about on the internet for this issue is to convert the inputted time e.g 156:62 (if that is possible?) into two separate fields one for the hours and one for the minutes and then merge the two together on export.

Ideally on my form I would like the user to be able to input something like 156:62 and then everything else calculate itself from there using calculated fields. How would I go about navigating the minefield of different formats and which expressions would I need for the hours and minutes columns?

Would something like LEFT(string,length) work and maintain the numerical format or would I have to use it as text?

Would this also affect my exported format of hhh:mm coming out of access, if not how would I preserve it?

You'll need to write this out as a function of minutes in order to display it that way. This is what the Doc_Man was referring to about treating the values as dates or numbers.

The user is meant to enter the duration of the delay as opposed to having to enter 2 times.

The delay could be something like 36 hours and 16 minutes where the delay initially started on one day and then was resolved the day after the next.

Ideally I would like my user to enter just the duration of the delay e.g 36:16 and then the form/database could automatically calculate and store this information in a format which can then end up being spat back out into Excel in the exact same format that it was entered in. This being 36:16. Ideally this would be spat out in a time format so that the user could then do some time sums on it easily in Excel.

Any tips for how to make it so that in my duration entry box on my form can take hh:mm (values greater than 24 hours) and then break it down into hours and minutes?? Ideally I would like to have just 1 input box on my form for the duration rather than to have 2 boxes (one for hours and one for minutes).

To use hours greater than 24, you need a custom subroutine to manipulate the data. The formatting functions, .e.g. Format( x, "hh:nn" ), will not handle X where X > 23:59 because that isn't really a standard time.

is just a simple short time in the format hh:mm.

Click to expand...

That didn't answer the question, but the subsequent discussion implies that your users enter the hours and minutes as text separated by a colon. But to what kind of field?

We see a lot of Excel-related code. You said you wanted an Excel output, but what is the datatype on the field in Access? Or... does the table not EXIST in the Access file and instead is a mapped EXCEL table?

For what it is worth, the answer for Access will certainly be SIMILAR to the answer for Excel, since they share a lot of routines in the libraries they use. But we need to know some of the context.

I'm hoping that this answers your question, but I didn't fully understand what you were asking for, so here goes...

Yes, the user will input something which will typically be a 2 digit hour value, followed by a colon, followed by a 2 digit minute value. E.g 15:25 for 15 hours and 25 minutes duration of delay.

In some rare cases, the user is going to have to input a 3 digit hour value which will end up being something like 105:35 for 105 hours and 35 minutes duration of delay.

In terms of the format stored by the database, for me, it can be in any format, as long as when I come to create a query to export into Excel, the time can be made to appear in a [hh]:mm format so that when it is in Excel, I can highlight over the cells and it will show me a summed time etc.

Maybe this is achieved by having a text box input on the form which takes a short text string e.g 105:35 and puts it into a field called DelayTime. We then have 2 columns which calculated the respective hours and minutes from the text string. In effect DelayHours column has a value of 105 and DelayMins has 35. Then when it comes to exporting into Excel can we make it so that Access lumps the two parts back together with a colon in the middle and formatted as a duration/time?

This is just an idea running through my mind and I have no real method for how to actually execute this.

[EDIT]

I have had a little play around using some of the formulae that I know and struggling around with stuff that I definitely don't know and came up with this (Note: when I say I have changed something, I have also created a backup so I can always revert to the original so I haven't fully committed to the changes):

I have changed my DelayTime duration input box on my form to have a 000:00;0;_ input mask. The Control source that this is linked to is DelayTime in my main table. I have set DelayTime as Short Text with an input mask of 000:00;0;_ . I have changed the DELAY HRS field to be calculated using the following formula:

and set it as a Double with the format 00.

For DELAY MINS I have used the exact same properties as with DELAY HRS except the expression is now Right([DelayTime],2). From here how do I concatenate the two to enable me to export them as a time/duration format again?

It is always going to come down to where you get the input and how you store it. If you know that, then you can talk about how to display it.

If your input is "nnn:nn" as a text string, then your formula to use Instr to extract the Left (=hours) and Right (=minutes) separately will allow you to store the CLng or CInt of the hours and minutes as simple numbers. By the way, to simplify the process, look up the Split function. But if you need to preserve the "nnn:nn" format, why do you not just copy THAT value, literally as it was input, to that third "formatted" field?

You said you needed all three fields - an hours field, a minutes field, and a formatted "hrs:min" field. This would give you all three. Just put your code to split the fields in the OnChange or LostFocus event of the box where your user enters the number. Or if this is something entered via an input box, just do the split before you leave the routine that triggered the input box.

I don't see a difficulty. Or am I missing something here?

The issue I was having was that if I stored the short text string for hhh:mm and then exported it to Excel with the format [hh]:mm, the numbers would look correct and the cells in the Excel file would be formatted to be [hh]:mm, but they wouldn't actually be updated. It seems to be a common issue with Excel, the cell formats often don't update unless you manually edit the cell. It's an issue you should be able to come across pretty easily when you type in something like "Excel cells won't update with format".

I had a little stab at trying to figure it out myself and managed to come up with a bit of a convoluted method, but it works.

Whilst googling "Excel cells won't update with format", I saw a post saying that you can update lots of cells quickly by using a find and replace and replacing a character with the same character. E.g with my times, the colon : is common in every exported cell.

I then went into my Exported Excel file and used the macro recorder to record the "touching up" steps that I wanted to automate in my vba. I didn't think the find and replace would have been recorded by the macro recorder, but it was and I then took the recorded snippet and added into the main export code.

This means that when I export, the code does some cell colour and font formatting and then does a find and replace on 1 column which has all the times. The cells are then updated and I can do a highlight select over the cells and the times will appear autosummed in the bottom right of my Excel window.