Formula to compare and amend date and time

**Bob Phillips** · 05-12-2010, 06:12 AM

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

**Saajan** · 05-12-2010, 06:26 AM

Thanks a ton!

That works a treat.. You are a gem!

I do not have enough words to thank you..

**teylyn** · 05-12-2010, 07:22 AM

Saajan,

maybe you have a little breath left to mark the thread as solved.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**Saajan** · 06-01-2010, 11:15 AM

Oops.. Sorry.. Was too excited!!! Will do that straight away.. Thanks!

EDIT: I am unable to edit any of my previous posts for some reason

**Saajan** · 06-01-2010, 12:12 PM

This is an extension of the same issue, so I wasn't sure if I had to create a new post.

Admin, if it needs to be a new post, please feel free to move it or guide me in doing so.

Ok.. Here is the problem...

How should the same formula be amended if column 'E' is in the time format 00:00:00 and column 'G' is formatted as general?

I tried to remove the time formatting from column 'E' and for some reason, it doesn't seem to like it. When I format it as text, it gives me some decimal values like 0.5, 0.3333, etc. I tried changing the formats in different ways, but still doesn't seem to work.

I would appreciate if you could show me some way of removing the time formatting or may be amend the formula so that it works with this format.

I hope I made sense with what I was trying to explain.

Any help would be greatly appreciated.. Many Thanks!

**DonkeyOte** · 06-01-2010, 12:20 PM

I think you will need to provide more detail.

Time in Excel is a Decimal value - ie 1 day (or 24 hours) equates to 1.0, thus 12 hours equates to 0.5, 6 hours 0.25 and 18 hours 0.75 etc...

If your values are both true Time values but one is formatted as Time and the other as General both values remain valid Times in their own right.
The underlying values are important, the formatting is irrelevant in such situations and you can disregard this concern accordingly.

IF your "Time formatted value" is using the Custom Format route previously discussed on this thread (ie entering 1230 appears as 12:30) then you must still account for this.

If in doubt, post a sample file.

**Bob Phillips** · 06-02-2010, 05:55 AM

Use

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

**Saajan** · 06-03-2010, 04:34 AM

Originally Posted by Bob Phillips

Use

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

Thanks a lot Bob.. That seems to be working.. You are simply great!

I have added you reputation.

Many Thanks again!

PS: Can admin/mod please mark this as SOLVED as I am unable to do so. Thanks!