Format Hours and Minutes in Access

Hi Elsa,

Thank you for the forum question.

When I first time read your question I was thinking this is an easy one, but I must say it turned out completely different.

Access is not very good at what you want. Access like a start time and a end time but not as you will like to type in 30:40.

I have been through a lot of testing and websites and I can tell you that many have the same problem. The format HH:MM (time formatting) can only handle max 24 hours and you cannot use any other formatting if you want to summarise the hours and minutes in a Pivot Table.

I managed to find a way of doing it. In the database there must be a field for hours and one for minutes. In the Pivot table you will also end with two columns. One for hours and one for minutes. To turn 70 Minutes to 1 hour and 10 minutes you can create some calculated fields.

I have attached a database with a table with few data. I have also attached a Excel workbook where you can find the formulas for the calculated fields you will need in the pivot table.

I hope this can do what you want.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector