Adding hours & minutes in excel

  no looks no brains no worries 19:55 24 Jan 04

Can anyone help me to enter validated times in excel. I have sorted out how to add times by formatting cells as [h]:mm. Problem arises if anyone using the sheet enters the hours and minutes as e.g. 5.20 instead of 5:20. That produces a date and time entry that messes up the calculations.
I've tried validating the entry against a list of times in the h:mm format but excel changes the list to to h:mm:ss so validation blocks the entry when they enter the time correctly as h:mm but accepts it when they enter it 'incorrectly' as How infuriating is that!
Please tell me how to turn off either the change of to a date and time, or the change of h:mm to h:mm:ss?

  Mango Grummit 20:05 24 Jan 04

To keep your post to the top.

Bit early for the drinker but when he turns up you can trust him (hic), it is Friday after all. He will be along later I betcha!

  VoG II 20:13 24 Jan 04

Well it's Saturday here so try this.

In the cell validation enter the minimum as 0:00:00 and the maximum as 23:59:59

Format the cell as [h]:mm

and it should work.

If not we'll try an OnEntry macro.

Thanks for the suggestion, unfortunately I forgot to mention that the value may be over 24hrs and the time validation entries wont accept it.

I'm actually trying to add up how many hours are worked by individuals on verious jobs in a month so an individual entry will sometimes be over 24 hrs.

I know there is specialist time keeping & billing software for this but it's only a temporary, quick and easy to use solution we need and that software is a bit beyond our needs.

Separate issue I know but looking through the forums I see loads of postings for the last few days. I haven't been able to access the PC Advisor site to get to the forums since Saturday, I thought it was down as I kept getting an error message. Scratching my head now to figure out why.

  Sheila-214876 11:19 28 Jan 04

I have written a "time sheet" in excel that covers 4 weekly periods, but as you say it won't go over 24 hours. The way round that is to put the times in up to 23.59 and then start at 00.01 and add the lost 2 minutes to the finish time. For example: 18.00 to 23.59 and then 00.01 to 07.02 instead of 07.00. If you would like a copy to try it please email me.


Thanx for the offer but I need something really simple and foolproof - says a lot about my confidence in the people I work with doesn't it lol.

Just needs to add up the manually enetered sub-totals of hours and minutes entered to keep track of our overtime budget. As I said those sub-totals might be more than 23 hours 59 mins and that's where the trouble really starts.

Looks like I'll just have to hope everyone enters it in the right format, otherwise the totals will be wrong, which means me getting the blame off the boss. Ain't life a b*tch!

  VoG II 17:07 29 Jan 04

Have a look at click here "Fixup for Dates and Times entered as Text" about a third down the page.

  jpallen 21:21 29 Jan 04

When you format the cell, click on custom and type "[hh:mm]" -(note they a square brackets). This allows the cells to total more than 24 hours; you will still have to use the colon.

  Simsy 07:05 30 Jan 04

that you have 2 different isssues here;

1) Adding up the totals to show more than 24 hours as a total...

2) Making it acceptable for a time entry to be made without typing the colon (:)

The first problem is covered by the last entry, from jpallen. The second can be covered using a macro.

I have done something similar on a timesheet, but it has some very specific characteristics and wouldn't be suitable for copying here. If you do want to persue this route please indicate. I manipulated a macro I found elswhere to suit my needs. It may be that it can be suitably further modified to suit yours.



Thanks to everyone who posted. What I've done works fine for our purposes as long as people use the correct format for entries. I'm just going to have to trust them to do that.

This thread is now locked and can not be replied to.

Microsoft Surface PC release date, price and specs: All-in-one Surface PC to directly rival iMac

1995-2015: How technology has changed the world in 20 years

Best Photoshop video tutorials: 8 video tutorial websites for Photoshop

Apple's event invitations decoded: A look back at 16 of Apple's most cryptic invites | Clues in…