How do I format periods of time and then add them up

[LucyGood]

Hi

I have to provide works logs for my clients, showing how many hours I have worked each day. I then need to tally this up each week. I have attached an example, from which I have to add up everything manually.

I have spent hours looking for a easy way to do this in Excel, but without luck. Surely Excel is able to add these chunks of time up for me?

If I change the format of the cells to hh:mm:ss, and then try to put say 2 hours 45 minutes into the cell, it shows something totally different.

Can someone please explain how I can enter periods of time in my log, and then have them automatically totalled.

Any help would be massively appreciated.

Thank you in advance.

Lucy

[jkray622]

Hi Lucy,

Those times are not "lengths of time", but a time of day. When you enter 2:45, it's thinking of 2:45am or pm. Off the top of my head, you should either enter a Start Time & End Time for each transaction, or enter the length of time in minutes.

Something is weird about your sheet...when I try to click on a cell, it almost always selects multiple rows beneath the selected cell as well. What did you do to it?

[Tony Valko]

Change the format to h:mm then enter the time like this:

For:

1h 30m enter 1:30
10h 0m enter 10:00
0h 45m enter 0:45

Then, to get the sum total just use a simple SUM(...) function:

=SUM(D2:D22)

Format the sum as [h]:mm

The brackets [ ] keep the hours from rolling over into days.

[daddylonglegs]

Hello Lucy,

Yes - try formatting the cells as hh:mm.......and when you enter the times you still have to use a colon, e.g. enter 2:30 for 2 hours 30 minutes

Now you can use a simple SUM function to sum the times, e.g.

=SUM(D2:D16)

Format the result cell with custom format [h]:mm - the square brackets allow totals greater than 23:59 to be displayed

[Toonies]

hi try this

Log Example.xlsx

[LucyGood]

Thank you so much to everyone for your helpful comments.

I think I have finally done it!

I know that I would NEVER have worked that one out on my own.

THANK YOU again.

[Tony Valko]

You're welcome. We appreciate the feedback!