Old topic - New Problem - Date format in 2007 Access

[teachesms]

Hans, you told me in 2003 Access to format a formula result as h:mm in a query to get the correct format in a report which was based on the query to sum time fields.
The original message went like this:

I said: I need a way to convert the answer it gives me from minutes to total hours. When I simply divide by 60 it gives me the number as a percentage of the hour in decimal form. I need the actual hours, for example if the total times of lab work ended up being 108 minutes, I want the field to show 1:48 for one hour and 48 minutes.

You said: Instead of dividing by 60, divide by 1440 (the number of minutes in a day) and format the result as h:mm

You have to set the Format property of the text box to h:mm
That tells Access that you want the result to be formatted as a time value instead of a number.

ALL OF THIS WORKED FINE UNTIL I HAD TO CONVERT THE DATABASE TO 2007 for the girls up stairs, and now the formula in the columns work, but the grand total does not sum. AND THERE IS no where to type h:mm on the format line for the text box...it only allows to choose from prechoice options like short time, long time, medium time...allowing no input of formatting like h:mm any more. What do I do to fix the crazy grand total to add up what is in the column? And there is no option to format to h:mm for the text box either. Just didn't add the picture.

Adding a pic of report in design view and query in design view, and the format box option without the h:mm option

[HansV]

1) If the grand total doesn't sum anymore, what does it do instead?

2) You should be able to type a custom format such as h:mm in the Format property - just ignore the dropdown list.

[teachesms]

It says 4:30 and when I type h:mm into the format property line it changes to h:nn and ignores me.

[HansV]

h:nn is OK - it's the "official" format notation. m is used for months and n for minutes, but for convenience it lets you enter h:mm.

[HansV]

By the way, Nannette, notifications sent to the Yahoo e-mail address in your profile are being bounced with message: "This user doesn't have a yahoo.com account".
If you have a new e-mail address, please go to your User Control Panel > Profile > Edit Account Settings and enter the correct e-mail address, then click Submit.

[teachesms]

Did not help. I used the h:nn as you noted. Did not change the grand total 4:30 I even exported the report over to excel, summed up the column of time there and it says 4:30. It's so crazy around here today...any other ideas? Should be around 2 hundred something

[HansV]

The h:nn format is still a clock time format - the hours go up to 23, then start over at 0 again.
Unlike Excel, Access does not have a format for cumulative time that will display (for example) 75 hours and 48 minutes as 75:48.

If you want to display cumulative time in Access, you have basically two choices:

1) Display decimal hours, so 75 hours and 48 minutes would be 75.80 (since 48 minutes = 0.80 hours). This can be accomplished by using =Sum([TIME])/60 and formatting as 0.00.

or

2) Display as a text string:

=(Sum([TIME]) \ 60) & ":" & Format(Sum([TIME]) Mod 60, "00")

[teachesms]

I used it. I come out with 556:30 minutes. She swears that there is only 200 some hours in there. I'm going to have to count the hours by hand myself. I'll come back tomorrow as it's quitting time here, and I've had a very long day...and this is quite the end of it. :) Thank you Hans. If your formula is correct and we have >500 hours hooray...but if she really does have a few > 200 then I'm still in a quandry.

[HansV]

You might also export the data to Excel and sum them. If you have a number of minutes in (say) cell A100, enter the formula =A100/1440 in another cell to convert it to a date/time value, and format the cell with the formula as [h]:mm - the square brackets around the hour tell Excel to treat it as cumulative time. (As I mentioned, Access doesn't support the [h]:mm format, unfortunately)

[teachesms]

What happened? Is this because of the conversion? Everything worked before the 2007 conversion.

If I Export the Access table send it over to excel I receive 33390 minutes. (I have a minute column) and I simply summed up the minutes. Then I did =I311/1440 like your example above and then formatted that as [h]:mm like you said. It gave me 556:30. Am I to assume this is 556 hours and 30 minutes?

I also simply added up the minute column in the Access 2007 table (it can sum now) and received 33390 in the total for the minutes column. Without having to stop and get my dang calculator out and go over each and every entry, I don't know what else to do.

At this point, the numbers seem to work out to >500 hours, and I think her math is wrong...I'm going to see how this works in the rest of the reports that run like this and have lost their working formulas since the conversion.

Thank you Hans, I'll let you know how it goes...

[HansV]

33390 minutes indeed corresponds to 556 hours and 30 minutes.

Access has never had a cumulative time format. If you want to display time values over 24 hours, you have always had to resort to methods such as outlined in Post=16193 higher up in this thread; this hasn't changed between Access 2003 and Access 2007.
Perhaps you never had to deal with totals above 24 hours before?

[teachesms]

I finally got he formulas to work using your previously given suggestion of: =(Sum([SumOfMINUTES])\60) & ":" & Format(Sum([SumOfMINUTES]) Mod 60,"00")
It just clicked finally and applied that same formula to all the reports and they now add up okay. I also applied the h:nn format to the query the information is being pulled from (and that is grouped and summed as well by the minutes field)

I am posting another question however related to the layout of the report. Just wanted you to know on this post that it's all working well, and I really appreciate all your help. Not once have I ever felt alone when working on stuff that makes me nuts...you guys and gals are always there, and words cannot express how much that means to me, and I am certain everyone of us who come to you on this board. Thank you Hans.

[HansV]

Thanks for your kind words!