| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If Functions Time
A1 = Time 1, B1 = Time 2, C1 = Time Difference
If A1=B1 then C1= "On Time" If A1B1 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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
Sorry if I wasn't clear...I need a formula for this. Thanks.
"Imbecile90" wrote: A1 = Time 1, B1 = Time 2, C1 = Time Difference If A1=B1 then C1= "On Time" If A1B1 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
If the times were always within the same day, you could use a formula like:
=IF(A1=B1,"On time",IF(A1B1,"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???? Imbecile90 wrote: Sorry if I wasn't clear...I need a formula for this. Thanks. "Imbecile90" wrote: A1 = Time 1, B1 = Time 2, C1 = Time Difference If A1=B1 then C1= "On Time" If A1B1 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. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
Thank you Dave. They'll always be on the same day within 5 or 6 hours. Thanks
again. "Dave Peterson" wrote: If the times were always within the same day, you could use a formula like: =IF(A1=B1,"On time",IF(A1B1,"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???? Imbecile90 wrote: Sorry if I wasn't clear...I need a formula for this. Thanks. "Imbecile90" wrote: A1 = Time 1, B1 = Time 2, C1 = Time Difference If A1=B1 then C1= "On Time" If A1B1 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. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
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. "Imbecile90" wrote: Thank you Dave. They'll always be on the same day within 5 or 6 hours. Thanks again. "Dave Peterson" wrote: If the times were always within the same day, you could use a formula like: =IF(A1=B1,"On time",IF(A1B1,"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???? Imbecile90 wrote: Sorry if I wasn't clear...I need a formula for this. Thanks. "Imbecile90" wrote: A1 = Time 1, B1 = Time 2, C1 = Time Difference If A1=B1 then C1= "On Time" If A1B1 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. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
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 "Billy Liddel" wrote: 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
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. "Imbecile90" wrote: 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 "Billy Liddel" wrote: 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
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. "Imbecile90" wrote in message ... 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. "Imbecile90" wrote: 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 "Billy Liddel" wrote: 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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
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. "Fred Smith" wrote: 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. "Imbecile90" wrote in message ... 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. "Imbecile90" wrote: 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 "Billy Liddel" wrote: 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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
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 "Imbecile90" wrote in message ... 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." . Thank you so much. "Fred Smith" wrote: 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. "Imbecile90" wrote in message ... 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. "Imbecile90" wrote: 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 "Billy Liddel" wrote: 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. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
Perfect. Thank you so much.
"Fred Smith" wrote: 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
Hi, What do I have to add to the formula make B1 blank if there is no information in A1? |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
=IF(A1="","","A1 not blank")
Gord Dibben MS Excel MVP On Mon, 15 Mar 2010 17:44:01 -0700, Imbecile90 wrote: Hi, What do I have to add to the formula make B1 blank if there is no information in A1? |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If Functions Time
I mean C1 blank if there is nothing in A1
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set my time and get rid of sample time using functions | Excel Worksheet Functions | |||
| Time functions | Excel Worksheet Functions | |||
| Calculating time without using time functions... | Excel Worksheet Functions | |||
| Time Functions | Excel Worksheet Functions | |||
| Time Functions | Excel Worksheet Functions | |||
Linear Mode
