Time log round up and down issue

binzitari

New Member
Joined
Nov 4, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am starting a job where i need to keep track of my own time for projects and clients. I have made an excel workbook to assist with that but I am having trouble with the end rounding function.

I am having the function round to the nearest .1 of an hour (or six minutes). The function I have is [Roundup((End time - Start time) *24,1] and it works perfectly. However the issue I am coming into is if I were to put in 5 different 1 minute intervals it would tell me that I have worked .5 hours which is not true. What is the way that i can modify the function to take into consideration the previous intervals when doing it rounding so that I can more accurately keep my time.



PS, I do know that there is a possible way to do this as when I was interning a former co worker designed a spreadsheet that did just this, but I am no longer able to contact him to help/send a template as he was fired and his files were lost.

Thank you,
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
3,937
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

Perhaps:

ABCDE
1StartEndDuration ([h]:mm:ss)Duration (roundup 0.1 hrs)Adjusted duration (roundup 0.1 hrs)
28:21:00 AM8:45:30 AM0:24:300.50.5
38:50:00 AM8:57:00 AM0:07:000.20.1
49:00:00 AM9:04:00 AM0:04:000.10.0
59:05:00 AM9:10:00 AM0:05:000.10.1
69:20:00 AM9:22:00 AM0:02:000.10.1
79:23:00 AM9:27:00 AM0:04:000.10.0
Sheet1
Cell Formulas
RangeFormula
C2:C7C2=B2-A2
D2:D7D2=ROUNDUP(24*C2,1)
E2:E7E2=ROUNDUP(24*(SUM(C$2:C2)),1)-SUM(E$1:E1)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,207
Messages
5,570,903
Members
412,346
Latest member
JGordon11
Top