How to calculate how much I've worked per day?

microsoft-excel spreadsheet worksheet-function

3,783

Solution 1

Try this formula:

=24*(B1+(A1>B1)-A1)

Where:
A1 is your Start Time ("Started At")
B1 is your End Time ("Left At")

It'll help you compute the number of hours elapsed between 2 time stamps. It should work even if your "End Time" is past midnight (or even 2 days later). Don't forget to set the cell format to General or 0.00.

Some examples:

enter image description here

If you want to get just the time elapsed (non-decimal or in [h]:mm:ss format), use the formula below and change the cell format to Custom > [h]:mm:ss.

=B1+(A1>B1)-A1

Solution 2

The problem is that the way you're storing your time, Excel treats it as a number between 0 and 1. I'm sure you realize that, but that means that if you leave at some time AM, but arrive in the PM then you'll get a negative number.

To avoid this, you can use something like this:

=(IF(B1>A1,B1-A1,B1+1-A1))*24

This will subtract your arrival time from your departure time correctly. If Excel thinks your departure time is before your arrival, the +1 will push it to the next day as desired.

Solution 3

You can add times using the =SUM worksheet function. Just enter all of your times as HH:MM:SS, and then use SUM to add them up. You may leave off the :SS if you prefer. By default, Excel will display the sum of times in "time-of-day" format, meaning that adding 12:30 + 12:45 will yield 01:15. You can prevent Excel from "rolling over" at 24 hours by formatting the result cell as [h]:mm which will cause it to display 25:15 rather than 01:15.

If you want to add up minutes and seconds, you must include a leading "0:" in your data.

For example, enter "0:10:20" to indicate 10 minutes, 20 seconds. When you sum these times, Excel will display the sum in "time-of-day" format, meaning that adding 0:40:10 and 0:30:20 will yield 1:10:30. You can prevent Excel form "rolling over" at the hour by formatting the result cell as [m]:ss which will cause it to display 70:30 rather than 1:10:30.

Another method of adding times is to use the TIME function. To add 1 hour, 35 minutes, 10 seconds to a time in A1, use the function:

=A1 + TIME(1,35,10)

Solution 4

The only way that I know how is to use military time.

View more solutions

3,783

Author by

Mladen Rakonjac

Updated on September 18, 2022

Comments

Mladen Rakonjac over 1 year

Here is my AppDatabase.kt

@Database(entities = arrayOf(Loan::class), version = 3)
abstract class AppDatabase : RoomDatabase() {

    abstract fun loanModel(): LoanDao

    companion object {
        private const val DB_NAME = "loans.db"

        fun createPersistentDatabase(context: Context): AppDatabase
                = Room.databaseBuilder(context.applicationContext, AppDatabase::class.java, DB_NAME).build()
    }
}

I am getting this error:

 e: org.jetbrains.kotlin.util.KotlinFrontEndException: Exception while analyzing expression at (11,54) in /Users/mladenrakonjac/MyFirstKotlinApp/app/src/main/java/me/mnemonic/myloan/data/AppDatabase.kt:
3

    at org.jetbrains.kotlin.types.expressions.ExpressionTypingVisitorDispatcher.logOrThrowException(ExpressionTypingVisitorDispatcher.java:250)
    at org.jetbrains.kotlin.types.expressions.ExpressionTypingVisitorDispatcher.lambda$getTypeInfo$0(ExpressionTypingVisitorDispatcher.java:221)
    at org.jetbrains.kotlin.util.PerformanceCounter.time(PerformanceCounter.kt:90)
    at org.jetbrains.kotlin.types.expressions.ExpressionTypingVisitorDispatcher.getTypeInfo(ExpressionTypingVisitorDispatcher.java:171)
    at org.jetbrains.kotlin.types.expressions.ExpressionTypingVisitorDispatcher.getTypeInfo(ExpressionTypingVisitorDispatcher.java:142)
    at org.jetbrains.kotlin.types.expressions.ExpressionTypingServices.getTypeInfo(ExpressionTypingServices.java:121)
    at org.jetbrains.kotlin.resolve.calls.ArgumentTypeResolver.getArgumentTypeInfo(ArgumentTypeResolver.java:235)
    at org.jetbrains.kotlin.resolve.calls.ArgumentTypeResolver.analyzeArgumentsAndRecordTypes(ArgumentTypeResolver.java:379)
    at org.jetbrains.kotlin.resolve.calls.CallResolver.doResolveCall(CallResolver.java:600)
    at org.jetbrains.kotlin.resolve.calls.CallResolver.doResolveCallOrGetCachedResults(CallResolver.java:529)
    at org.jetbrains.kotlin.resolve.calls.CallResolver.lambda$computeTasksFromCandidatesAndResolvedCall$1(CallResolver.java:210)
    at org.jetbrains.kotlin.util.PerformanceCounter.time(PerformanceCounter.kt:90)
    at org.jetbrains.kotlin.resolve.calls.CallResolver.computeTasksFromCandidatesAndResolvedCall(CallResolver.java:206)
    at org.jetbrains.kotlin.resolve.calls.CallResolver.computeTasksFromCandidatesAndResolvedCall(CallResolver.java:196)
    at org.jetbrains.kotlin.resolve.calls.CallResolver.resolveConstructorCall(CallResolver.java:365)
    at org.jetbrains.kotlin.resolve.calls.CallResolver.resolveCallForConstructor(CallResolver.java:350)
    at org.jetbrains.kotlin.resolve.calls.CallResolver.resolveFunctionCall(CallResolver.java:282)
    at org.jetbrains.kotlin.resolve.calls.CallResolver.resolveFunctionCall(CallResolver.java:253)
    at org.jetbrains.kotlin.resolve.AnnotationResolverImpl.resolveAnnotationCall(AnnotationResolverImpl.java:151)
    at org.jetbrains.kotlin.resolve.lazy.descriptors.LazyAnnotationDescriptor.computeValueArguments(LazyAnnotations.kt:143)
    at org.jetbrains.kotlin.resolve.lazy.descriptors.LazyAnnotationDescriptor.access$computeValueArguments(LazyAnnotations.kt:108)
    at org.jetbrains.kotlin.resolve.lazy.descriptors.LazyAnnotationDescriptor$valueArguments$1.invoke(LazyAnnotations.kt:126)
    at org.jetbrains.kotlin.resolve.lazy.descriptors.LazyAnnotationDescriptor$valueArguments$1.invoke(LazyAnnotations.kt:108)
    at org.jetbrains.kotlin.storage.LockBasedStorageManager$LockBasedLazyValue.invoke(LockBasedStorageManager.java:323)
    at org.jetbrains.kotlin.storage.LockBasedStorageManager$LockBasedNotNullLazyValue.invoke(LockBasedStorageManager.java:364)
    at org.jetbrains.kotlin.resolve.lazy.descriptors.LazyAnnotationDescriptor.getAllValueArguments(LazyAnnotations.kt:140)
    at org.jetbrains.kotlin.resolve.lazy.descriptors.LazyAnnotationDescriptor.forceResolveAllContents(LazyAnnotations.kt:161)
    at org.jetbrains.kotlin.resolve.lazy.ForceResolveUtil.doForceResolveAllContents(ForceResolveUtil.java:75)
    at org.jetbrains.kotlin.resolve.lazy.ForceResolveUtil.forceResolveAllContents(ForceResolveUtil.java:68)
    at org.jetbrains.kotlin.resolve.lazy.descriptors.LazyClassDescriptor.resolveMemberHeaders(LazyClassDescriptor.java:535)
    at org.jetbrains.kotlin.resolve.lazy.descriptors.LazyClassDescriptor.doForceResolveAllContents(LazyClassDescriptor.java:521)
    at org.jetbrains.kotlin.resolve.lazy.descriptors.LazyClassDescriptor.lambda$new$4(LazyClassDescriptor.java:220)
    at org.jetbrains.kotlin.storage.LockBasedStorageManager$LockBasedLazyValue.invoke(LockBasedStorageManager.java:323)
    at org.jetbrains.kotlin.resolve.lazy.descriptors.LazyClassDescriptor.forceResolveAllContents(LazyClassDescriptor.java:517)
    at org.jetbrains.kotlin.resolve.lazy.ForceResolveUtil.doForceResolveAllContents(ForceResolveUtil.java:75)
    at org.jetbrains.kotlin.resolve.lazy.ForceResolveUtil.forceResolveAllContents(ForceResolveUtil.java:41)
    at org.jetbrains.kotlin.resolve.jvm.extensions.PartialAnalysisHandlerExtension$doAnalysis$1.invoke(PartialAnalysisHandlerExtension.kt:66)
    at org.jetbrains.kotlin.resolve.jvm.extensions.PartialAnalysisHandlerExtension$doAnalysis$1.invoke(PartialAnalysisHandlerExtension.kt:34)
    at org.jetbrains.kotlin.resolve.jvm.extensions.PartialAnalysisHandlerExtension.doForEachDeclaration(PartialAnalysisHandlerExtension.kt:119)
    at org.jetbrains.kotlin.resolve.jvm.extensions.PartialAnalysisHandlerExtension.doForEachDeclaration(PartialAnalysisHandlerExtension.kt:133)
    at org.jetbrains.kotlin.resolve.jvm.extensions.PartialAnalysisHandlerExtension.doAnalysis(PartialAnalysisHandlerExtension.kt:61)
    at org.jetbrains.kotlin.kapt3.AbstractKapt3Extension.doAnalysis(Kapt3Extension.kt:145)
    at org.jetbrains.kotlin.resolve.jvm.TopDownAnalyzerFacadeForJVM.analyzeFilesWithJavaIntegration(TopDownAnalyzerFacadeForJVM.kt:97)
    at org.jetbrains.kotlin.resolve.jvm.TopDownAnalyzerFacadeForJVM.analyzeFilesWithJavaIntegration$default(TopDownAnalyzerFacadeForJVM.kt:76)
    at org.jetbrains.kotlin.cli.jvm.compiler.KotlinToJVMBytecodeCompiler$analyze$1.analyze(KotlinToJVMBytecodeCompiler.kt:365)
    at org.jetbrains.kotlin.cli.common.messages.AnalyzerWithCompilerReport.analyzeAndReport(AnalyzerWithCompilerReport.kt:105)
    at org.jetbrains.kotlin.cli.jvm.compiler.KotlinToJVMBytecodeCompiler.analyze(KotlinToJVMBytecodeCompiler.kt:354)
    at org.jetbrains.kotlin.cli.jvm.compiler.KotlinToJVMBytecodeCompiler.compileModules(KotlinToJVMBytecodeCompiler.kt:139)
    at org.jetbrains.kotlin.cli.jvm.K2JVMCompiler.doExecute(K2JVMCompiler.kt:167)
    at org.jetbrains.kotlin.cli.jvm.K2JVMCompiler.doExecute(K2JVMCompiler.kt:55)
    at org.jetbrains.kotlin.cli.common.CLICompiler.exec(CLICompiler.java:182)
    at org.jetbrains.kotlin.incremental.IncrementalJvmCompilerRunner.compileChanged(IncrementalJvmCompilerRunner.kt:443)
    at org.jetbrains.kotlin.incremental.IncrementalJvmCompilerRunner.compileIncrementally(IncrementalJvmCompilerRunner.kt:301)
    at org.jetbrains.kotlin.incremental.IncrementalJvmCompilerRunner.compile(IncrementalJvmCompilerRunner.kt:128)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.execIncrementalCompiler(CompileServiceImpl.kt:452)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.access$execIncrementalCompiler(CompileServiceImpl.kt:99)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl$compile$1$$special$$inlined$withIC$lambda$1.invoke(CompileServiceImpl.kt:379)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl$compile$1$$special$$inlined$withIC$lambda$1.invoke(CompileServiceImpl.kt:99)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl$doCompile$2$$special$$inlined$withValidClientOrSessionProxy$lambda$1.invoke(CompileServiceImpl.kt:798)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl$doCompile$2$$special$$inlined$withValidClientOrSessionProxy$lambda$1.invoke(CompileServiceImpl.kt:99)
    at org.jetbrains.kotlin.daemon.common.DummyProfiler.withMeasure(PerfUtils.kt:137)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.checkedCompile(CompileServiceImpl.kt:825)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.access$checkedCompile(CompileServiceImpl.kt:99)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl$doCompile$2.invoke(CompileServiceImpl.kt:797)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl$doCompile$2.invoke(CompileServiceImpl.kt:99)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.ifAlive(CompileServiceImpl.kt:1004)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.ifAlive$default(CompileServiceImpl.kt:865)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.doCompile(CompileServiceImpl.kt:791)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.access$doCompile(CompileServiceImpl.kt:99)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl$compile$1.invoke(CompileServiceImpl.kt:378)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl$compile$1.invoke(CompileServiceImpl.kt:99)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.ifAlive(CompileServiceImpl.kt:1004)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.ifAlive$default(CompileServiceImpl.kt:865)
    at org.jetbrains.kotlin.daemon.CompileServiceImpl.compile(CompileServiceImpl.kt:336)
    at sun.reflect.GeneratedMethodAccessor89.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:346)
    at sun.rmi.transport.Transport$1.run(Transport.java:200)
    at sun.rmi.transport.Transport$1.run(Transport.java:197)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
    at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:683)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded

fmanco almost 12 years

Maybe this can help. Or this.
Darius almost 12 years

Personally I would enter date + time in a following manner mm/dd/yyyy hh:mm:ss and then subtract those cells. The cell that holds the subtraction formula needs following format: hh:mm:ss
Daniel almost 12 years

@Darius I prefer using [h]:mm:ss because that will display the number of hours over 24. Using hh:mm:ss will only display up to 24, and will effectively show the number of hours mod 24. This can be very misleading.
Darius almost 12 years

True, but who works over 24 hrs straight? :)
Darius almost 12 years

@Rafael I hope you getting paid well for that...
Daniel almost 12 years

@Darius, it's more relevant when you're summing a week or two which is pretty typical for payroll. :-)

Canadian Luke almost 12 years

Argh, you beat me to it
Douglas almost 12 years

What does the (A1>B1) bit do?
Ellesa almost 12 years

@Douglas It adds 1 (aka 24 hours) to B1 if the time in A1 is greater than B1 (when the End Time is past midnight).
Ellesa almost 12 years

@zpea Yep. Took me a while to dig one of my old answers that included this formula.
Ellesa almost 12 years

Even with using Military time, the OP's original formula would still return a negative value for time stamps that cross midnight. :(