• The forum (and all other resources in Chandoo.org) will be under maintenance and will be unavailable for a maximum of two hours on the first week of October 2023. The exact date, time and duration of the maintenance are subject to change. We regret the inconvience it may cause.
  • 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.

Displaying Time Difference with late & early tags

chirayu

Well-Known Member
Hi All,

Ok so how to explain this one - To start off I'm also a member of Ozgrid forum so I answer posts there too and I came across a post that was intriguing. Basically a member there asked How to display a time difference in a single cell in a text format (rather than a number format) with late and early condition.

In other word there would only be 3 cells & no helper columns - WorkTime (shift scheduled), TimeIn (When that person arrived), Time check result (the formula that would tell the difference) - something like the below:

WorkTime | TimeIn | Time check result
------------------------------------------------
8:00 AM | 8:00 AM | On Time

So basically it should have all the below conditions in 1 single formula in 1 single cell:


Singles -
- Hour late
- Hour early
- Hours late
- Hours early

- minute late
- minute early
- minutes late
- minutes early

Combinations -
- Hours late & minute late
- Hours late & minutes late
- Hours early & minute early
- Hours early & minutes early

- Hour late & minute late
- Hour late & minutes late
- Hour early & minute early
- Hour early & minutes early

So here is attached the file I made - Hope you like it :) - Feedback appreciated
 

Attachments

  • Time & Text v2.xlsx
    11.3 KB · Views: 79
Hi Walawalkar ,

I think the formula is complicated because the input is being treated as text ; since Excel times are merely numbers between 0 and 1 , the formula can be simplified if we treat the input as numeric.

I have not checked thoroughly , but this formula seems to give the same result :

=INT(ABS(B3-A3)/One_Minute/60) & " hour " & ROUND(MOD(ABS(B3-A3)/One_Minute,60),0) & " minutes" & IF(A3<B3," late"," early")

where One_Minute is the numeric value of "00:01".

Probably this can be simplified further.

Narayan
 
Thanks Narayank. I'm guessing you used the named range method? However wouldn't this require a helper cell whereby you can define "00:01:00" - The user wanted a formula that was based in a single cell. Also noticed that it says hour & minutes only. It doesn't do Hours & minute. So I modified your formula :p

Named Range:
Code:
=IF(INT(ABS(B3-A3)/One_Minute/60)>1,INT(ABS(B3-A3)/One_Minute/60)&" hours ",INT(ABS(B3-A3)/One_Minute/60)&" hour") & IF(ROUND(MOD(ABS(B3-A3)/One_Minute,60),0)>1,ROUND(MOD(ABS(B3-A3)/One_Minute,60),0)&" minutes",ROUND(MOD(ABS(B3-A3)/One_Minute,60),0)&" minute") & IF(A3<B3," late"," early")

Formula:
Code:
=IF(INT(ABS(B3-A3)/"00:01"/60)>1,INT(ABS(B3-A3)/"00:01"/60)&" hours ",INT(ABS(B3-A3)/"00:01"/60)&" hour") & IF(ROUND(MOD(ABS(B3-A3)/"00:01",60),0)>1,ROUND(MOD(ABS(B3-A3)/"00:01",60),0)&" minutes",ROUND(MOD(ABS(B3-A3)/"00:01",60),0)&" minute") & IF(A3<B3," late"," early")

I must say however - I have never used INT or ABS formula so I don't know what it does but I'll learn it :)
But modifying formulas is easy :p
 
Last edited:
Hi, I used all formulas here and either formula works for when time passes 12:00 am. Is there a formula that can identify as late someone that is supposed to start at 11:00 pm, but instead starts at 12:00 am. This is considered late, but the formulas above identify it as early by 22 hours. I have tried many different formulas that I had came up with plus the ones you have above and I still can't get one that works. What I am trying to do (if at all possible) is.... I have the Appointment Time, the arrival time and the Departure time. The emp that waits for more than 2 hours at his destination gets paid for the time (above the 2 hrs) but if the employee is late at his destination for more than 1/2 hr, he doesn't get paid for any wait time, if he is early, he get paid for any time above the 2 hours from the appointment time. All time must be calculated in .25 increments.
 

Attachments

  • Wait time pay.xlsx
    46.9 KB · Views: 8
asrivera
As You are a new member,
You've today read Forum Rules before You sent Your the first post here.
You seems to missed one part from Forum Rules.
>> Open a new thread from You challenge <<
No more replies here.
 
Back
Top