0

I have an excel sheet that displays the total number of regular hours and overtime hours in excel. I am trying to figure out how to add them together to get a Total hours worked in a month the excel sheet looks like the following

E00XXXX   James Smith     160:00   19:52
E00XXXX   John Doe        160:00   19:52
E00XXXX   Sue Patterson   160:00   19:52

I am trying to add the last two columns together to get something like this

E00XXXX   James Smith     160:00   19:52   179:52
E00XXXX   John Doe        160:00   15:22   175:22
E00XXXX   Sue Patterson   160:00   10:47   170:47

I don't have any code to try, because I honestly don't even know where to start. I would also like to state that this has to be done within a VBA and that I do not have control over the format in excel.

Community
  • 1
  • 1
Robert E. McIntosh
  • 5,557
  • 4
  • 26
  • 35

1 Answers1

1

To automatically format times that when added together exceed 24 hours, 60 minutes, or 60 seconds, you can use the following custom number formats and the SUM function in Excel:

Hours = [h]:mm:ss
Minutes = [m]:ss
Seconds = [ss]

Source

malta
  • 858
  • 1
  • 9
  • 17
  • Updated my question: I don't have control over the format of the excel sheet, only what is within the VBA macro, is there a way to change the format of a cell in VBA? – Robert E. McIntosh Feb 10 '14 at 15:43
  • Yes - have a look at this - http://stackoverflow.com/questions/8265350/how-to-change-format-of-a-cell-to-text-using-vba – malta Feb 10 '14 at 15:45