Thread: Formula to compare and amend date and time

Try this

=IF(A2+B2>C2+D2+"03:00","Early",IF(A2+B2<C2+D2-"03:00","Late","On time"))

Originally Posted by Saajan

Please note that there is existing data in the tables and the data in columns are entered as numbers only and not formatted as time or date.

Just noticed this bit. Can you elaborate, maybe with examples?

Originally Posted by Saajan

Thanks for your quick reply.

The date fields are entered in the right format, meaning dd/mm/yyyy. But the time fields are entered as general 1030, 915, etc. When I try changing this field to Time format, it just resets it to 00:00:00. I hope this provides a better understanding of the problem.

Your time and efforts are very much appreciated!

Then try

=IF(A2+TEXT(B2,"00\:00")>C2+TEXT(D2,"00\:00")+"03:00","Early",IF(A2+TEXT(B2,"00\:00")<C2+TEXT(D2,"00 \:00")-"03:00","Late","On time"))

May be I am doing it wrong... I highlight the 'H' column and insert the formula in the "fx" field and click on the green tick. Get the error "A value used in the formula is of the wrong data type."

Am I Missing something here?

Originally Posted by Bob Phillips

Then try

=IF(A2+TEXT(B2,"00\:00")>C2+TEXT(D2,"00\:00")+"03:00","Early",IF(A2+TEXT(B2,"00\:00")<C2+TEXT(D2,"00 \:00")-"03:00","Late","On time"))

Really appreciate this!

But do I need to change anything in the above formula?

I would really appreciate if you could make it as simple as you can for me. I have mentioned in my first post which column referes to what.

Many Thanks!

Try this

=IF(D2+TEXT(E2,"00\:00")>F2+TEXT(G2,"00\:00")+"03:00","Early",IF(D2+TEXT(E2,"00\:00")<F2+TEXT(G2,"00 \:00")-"03:00","Late","On time"))

Originally Posted by Bob Phillips

Try this

=IF(D2+TEXT(E2,"00\:00")>F2+TEXT(G2,"00\:00")+"03:00","Early",IF(D2+TEXT(E2,"00\:00")<F2+TEXT(G2,"00 \:00")-"03:00","Late","On time"))

Thank you very much for that. It looks much better now.

I think we are nearly there. The only problem is that if the work is completed on the same day at an earlier time, it still displays it as "On Time" whereas if it is completed on an earlier date, it is displayed correctly. It is only checked against date and not time. Also, if the work is completed on time, I need this to be left blank. I only need "Early" or "Late" indicated.

Many Thanks.. You have been a great help!

Any suggestions?

Thanks!

Originally Posted by Saajan

I think we are nearly there. The only problem is that if the work is completed on the same day at an earlier time, it still displays it as "On Time" whereas if it is completed on an earlier date, it is displayed correctly. It is only checked against date and not time.

My understanding is that there is a 3 hour window, so if it is completed 3 hours earlier than the appointment time that is not Early, but if it 3 hours 1 minute earlier, then that is early. The formula does exactly that.

Originally Posted by Saajan

Also, if the work is completed on time, I need this to be left blank. I only need "Early" or "Late" indicated.

Just delete the text -On Time- from the foirmula (but keep the quotes).

Sorry.. I should have been more specific.

Sometimes the work gets completed before the appointment time. Say for example, if the appointment time is 1400 on 15/05/2010 and the work gets completed at 1230 on the same day, then it should state as Early. But it is still showing up as "On Time".

With regards to the above example:

1359 = Early
1400 = On Time
1700 = On Time
1701 = Late

At present, it shows appointment time - 3hrs as early whereas it should show appointent time - 1 min as early.

I hope it makes better sense now.

Many Thanks for all your help!