• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating working hours [SOLVED]

N

nanobuns

New Member
Hi,


I have this data(see below details) how am i going to compute the total working hours. I used the formula =Columnb-ColumnA but the working hours doesnt show instead it only appear a long ####. I'm using the same formula with other time it works perfectly fine.


Column A Column B

Time in Time Out

21:00 - 06:00


I need your expertise with this.


Thanks in advance,
 
Hi nanobuns,


Welcome to the forums! What is your desired output for above example? If you enclose your formula (TimeOut-TimeIn) as `ABS((TimeOut-TimeIn)) and format cell as hh:mm you will get 16:00 is that correct answer?


Regards,
 
Welcome to the forum!


You are getting the #### because the time in col B is less than the value in col A, thus resulting in a negative number. Assuming that these are from different days, a workaround would be:

=B2-A2+(B2<A2)


The last portion of this formula will add 1 (aka, 1 day) if col B time is less than col A. This will then give you the correct time difference.
 
Last edited:
Hi Luke,


Learned one new point from your post, if add TRUE that means we are adding 1! Great! Thanks. :)


Faseeh
 
Luke M said:
Welcome to the forum!


You are getting the #### because the time in col B is less than the value in col A, thus resulting in a negative number. Assuming that these are from different days, a workaround would be:

=B2-A2+(B2&#60;A2)


The last portion of this formula will add 1 (aka, 1 day) if col B time is less than col A. This will then give you the correct time difference.
Click to expand...

Hi Luke, I hope you're going great

I'm having the same problem as the op and tried to use the formula you posted but excel won't accept it "The formula you typed contains an error"

Any advice...
 
Please help me with the formula for total logged in hours for the below mentioned criterias:
1) Some Agents may not have logged in for the day.
2) Its a Overlapping Shift: Starting from 22:00 ending at next day morning 09:00.
3) All times are in 24 hrs format.
4) Need a formula which gives me blank if either the logged-in or logged-out time is not mentioned.
 
Hi Syed,

Welcome to the forum!

It will be good if you start a new thread with a sample workbook with your inputs and desired output. This way you will get a quick result.

Regards,
 
malbarki said:
Hi Luke, I hope you're going great

I'm having the same problem as the op and tried to use the formula you posted but excel won't accept it "The formula you typed contains an error"

Any advice...
Click to expand...
Hi Malbarki,

In the migration from old forum to new forum, several characters like <, >, ', ", etc. got messed up and turned into strange codes. I've corrected my previous post to hopefully prevent further confusion. :DD
 
You must log in or register to reply here.
Back
Top