Spreadsheet Help

Camalot · 7 Jul 2010 at 19:56

Hi guys.

I'm working on a Kronos sheet to work out how many hours / minutes have been swiped by each collegue.

I need a formula to go into the end coloumns to add the total of each row to display it in hours and minutes, if you understand? At the moment it is flawed because it will add the minute up out of 100, obviously there is 60 minuts to 1 hour.

Anyone able to help me out?

Thanks,
Adam.

csmager · 7 Jul 2010 at 20:59

As a simple fudge off the top of my head, the hours column would be:

=A2+C2+ROUNDDOWN((B2+D2)/60,0)

(obviously expanded as necessary - A2, C2 etc are hours, B2, D2 etc are mins)

The mins column would similarly be:

=B2+D2-ROUNDDOWN((B2+D2)/60,0)*60

Camalot · 7 Jul 2010 at 22:34

Damn, i can't do it!

If i e-mail the file to anyone, could someone set them two coloums up for me?

I really need to get on an advanced spreadsheet course

andicole0 · 7 Jul 2010 at 22:36

Why not make each column a time format of hours and minutes then just add them up. No need to have two parts for each day.

Look at cell formats.

Andi.

Camalot · 7 Jul 2010 at 23:06

andicole0 said:
Why not make each column a time format of hours and minutes then just add them up. No need to have two parts for each day.

Look at cell formats.

Andi.

Because the totals per day need to be recorded. If they are time formatted then the total hours worked on any day cant go over 24.

edit - seem to have found a way around that, trying now.