help with if formula

Rubygas14

New Member
Joined
Jun 26, 2017
Messages
20
Hi i have 2 dates and using the below formula due to different months this is not calculating correctly

30/11/2018 21:1003/12/2018
08:00
Late

<tbody>
</tbody>

=IF(I3< l3,"ok","late")
<l3,"ok","late")
<l3,"ok","late")
Code:
<l3,"ok","late")[ 
Can Anyone Help 

Thanks</l3,"ok","late")[code]<l3,"ok","late")[></l3,"ok","late")
 
Last edited by a moderator:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Forum has corrupted your post, please edit and place spaces around any < > characters
 
Upvote 0
There's nothing wrong with that formula.
Check that your dates are real dates, rather than text.
 
Upvote 0
right i have checked the format and All are set to Date Format but still getting incorrect Data

Formula is
= IF (I3< l3,"ok","late").

thanks

 
Upvote 0
If you format both cells as General, what do you see?
 
Upvote 0
Use ISTEXT or ISNUMBER to determine if the dates are in fact numbers or text.

What are you comparing I3 against L3 ?

If I3 and L3 are numbers you should get "ok"
If I3 and L3 are text you will get "late"
 
Upvote 0
In that case they are text not date/time
You will need to convert them to genuine date/time
 
Last edited:
Upvote 0
i think i have found the problem just need help if i am right so I3,
30/11/2018 21:10 <
L3,
03/12/2018 08:00 which should show "OK" as it is true but the formula in L3 to get the Date is = IF(OR(K92=Hour),TEXT(Post,"dd/mm/yyyy")&TEXT(K92," "&"hh:mm"),TEXT(Pre,"dd/mm/yyyy")&TEXT(K92," "&"hh:mm")) Would that force to cell to show as text??

if so what could i use as this is in my macro and it goes in to an Formula Array..


i have just copied the values to seperate cells and tried the if formula and it works.

Also i have just realised if i f2 and enter on the I3 Cell it cal calculates but i have alot of data to do this with.

 
Upvote 0
That formula, will indeed convert the values to text.
Try this in place of the formula in post#1

=IF(DATEVALUE(I3)< DATEVALUE(L3),"ok","late")<datevalue(l3),"ok","late")< html=""></datevalue(l3),"ok","late")<>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top