How to calculate the time?

Question

Using Access 2003 Database

Table

EmpID Intime Outtime

001   090020   180000
002   060000   220000
003   030040   231020

So on…,

InTime and Outime Column Data type is Text.

090000 - hhmmss

Here I want to calculate the time like 090000 to 180000 is the office time, Remaining time is over duty time.

Expected Output

EmpID Intime Outtime WorkedTime OverdutyTime

001    090020 180000   080040
002    060000 220000   090000       070000
003    030040 231020   090000       101040

So on…,

It Should Display only 9 Hour in Worked time column, remaining comes to overduty time column.

Need Query Help

Answer 1

It seems to me that you need:

SELECT Format(IIf(CLng(OutTime) > 180000, CDate("18:00:00"), 
       CDate(Format(OutTime, "00:00:00"))) - IIf(CLng(InTime) < 90000, 
       CDate("09:00:00"), CDate(Format(InTime, "00:00:00"))), "hh:nn:ss") 
       As WorkedTime, 
       Format(IIf(CLng(InTime) < 90000, CDate("09:00:00") - 
       CDate(Format(InTime, "00:00:00")), 0) + IIf(CLng(OutTime) > 180000, 
       CDate(Format(OutTime, "00:00:00")) - CDate("18:00:00"), 0), "hh:nn:ss") 
       As OverdutyTime
FROM Table

I have broken up the lines for ease of reading.

I think there may be an error in your calculation in this line:

003    030040 231020   090000       101040