Displaying Time Difference with late & early tags

[chirayu]

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

 

[NARAYANK991]

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

 

[chirayu]

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

Named Range:

=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:

=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

 

[asrivera]

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.

 

[vletm]

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.