If Functions Time

  • Thread starter Thread starter Imbecile90
  • Start date Start date
I

Imbecile90

A1 = Time 1, B1 = Time 2, C1 = Time Difference
If A1=B1 then C1= "On Time"
If A1>B1 then C1 = "Late By h:mm"
If A1<B1 then C1 = "Early By h:mm"
Also A1 may be a PM time and B1 may be an AM time

Examples:
A1=10:00 PM, B1=10:00 PM, C1=On Time
A1=9:30 PM, B1=10:00 PM, C1=Early By 00:30
A1=12:15 AM, B1=10:00 PM, C1=Late By 2:15

This is killing me. Thank you in advance.
 
If the times were always within the same day, you could use a formula like:
=IF(A1=B1,"On time",IF(A1>B1,"Late","Early")&" by "&TEXT(ABS(A1-B1),"h:mm"))

But when one is on one day and the other is on a different day, that formula
won't work.

I think the safest thing to do is to enter both the date and time for both
cells. Then you won't have any problems.

But if you don't want to do that, is there a maximum number of hours that can be
late or early.

If I put
12:15 AM in A1
and
10:15 PM in B1

How do I know if I'm 2 hours late or 22 hours early?

Or even if the times are 3 days different????
 
The trouble is that although they may be on the same shift, they are not on
the same day. 12:15 AM is just 15 minutes and this is lower than 22:00 (10:00
pm)

If 00:15 is in A1 this is lower than 22:00 just as 21:30 (9:39 PM)

By the way, what times does the shift cover? I do not know if this makes any
difference.
 
You're right, Buddy. If A1 = 12:15 AM AND B1 = 11:30 PM...I would need C1 to
be "Late By 45 mins" and instead it's "Early by 23:15"

Shift would be 8 PM - 8 AM
 
Adding a date is the most logical solution, but it's not practical in my
case. Is there a way I could create a custom 24 hour clock somehow? Sorry to
be a pain.
 
Try this:
=IF(A1=B1,"On Time",IF(ABS(B1-A1)<0.5,IF(A1<B1,"Early by
"&TEXT(B1-A1,"h:mm"),"Late by "&TEXT(A1-B1,"h:mm")),IF(A1+1<B1,"Early by
"&TEXT(B1-A1-1,"h:mm"),"Late by "&TEXT(A1+1-B1,"h:mm"))))

Regards,
Fred.
 
Fred,
We're so close...but if A1 = 11:30 PM and a B1 = 1:00 AM, I would need C1 to
be "Early by 1:30" instead it's "Late by 22:30." It's like my "day" STARTS at
about 7:00 PM and ENDS at about 8:00 AM. Thank you so much.
 
It's like my "day" STARTS at about 7:00 PM and ENDS at about 8:00 AM

OK, then try this:
=IF(A1=B1,"On Time",IF(MOD(A1-0.75,1)<MOD(B1-0.75,1),"Early by
"&TEXT(MOD(B1-A1,1),"h:mm"),"Late by "&TEXT(MOD(A1-B1,1),"h:mm")))

Regards,
Fred
 
Perfect. Thank you so much.

Fred Smith said:
OK, then try this:
=IF(A1=B1,"On Time",IF(MOD(A1-0.75,1)<MOD(B1-0.75,1),"Early by
"&TEXT(MOD(B1-A1,1),"h:mm"),"Late by "&TEXT(MOD(A1-B1,1),"h:mm")))

Regards,
Fred
Click to expand...
 
Back
Top