IF's

**Okley70** · 11-24-2020, 07:21 AM

Hello,

I have the =now() to show the current time in c1.

I have up to 40 different departure times for outbound shipping.

example column a1-a3

11:00 am
11:15 am
11:30 am

I would like b1-b3 to display if the load is late based on the current time. Now the issue I am having is that these time change each day so entering a hard formula 11,0,0 wouldn't work

=if(now()>a1,"late") only works once. if the time is changed is still shows late when I input a time in a1 that I know is early.
I have also tried replacing =now() with c1 and it still doesn't work.

Any help would be greatly appreciated. Happy thanksgiving

**Pepe Le Mokko** · 11-24-2020, 09:32 AM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

**Pepe Le Mokko** · 11-24-2020, 09:32 AM

And when it's done, please post a sample sheet ( see yellow banner). Thx

**Okley70** · 11-24-2020, 01:49 PM

I figured it out. Thanks

I am unable to attach the sheet.

I have loads of product that need to be loaded and departed before the departure time. example 11:00 am departure. If a loader picks up the load at 7:00 am, a column displays the time variance before the load is due.

I used =if(((h6-$q$1)*24)<1,(h6-$q$1)+24,(h6-$q$1)) to get the difference between the current time and the due time. =text(now(),"h:mm:ss") which was 4 hours. Then I did =hour(j6)*60+minute(j6)+second(j6)/60 to break it down in minutes. Then I used a macro to refresh the time.

The only issue is that when the time runs out it starts back over at 23:59. I can deal with this because it's clear at that point the load is late.