Possible nested functions?

[peterderrington]

Hi,

I'm sure what I'm trying to achieve is easy but my brains not in gear today....

I have a spread sheet which contains 2 rows (E and I) that each have a date stamp in them, what I want to do is calculate if the two dates are on the same day and if so return a value.

I firstly tried =DATEDIF(E3,I3,"D") which returns either a 0,1 or #NUM !

What I was wanting was to tidy up that response, to 0 = True, 1 = False and #NUM ! = Blank

I came close when I referenced the cell that the above formula was in like this: =IFERROR(Z3<1,"No")

But I'm guessing that I need to somehow nest the two formulas together.

Can anyone help?

Thanks

 

[AlanY]

excel store dates as number, e.g. today 6th Aug 2019 is 43682, so compare dates is just compare numbers.

a simple =if(E3=I3, 0,1) should do it

 

[tyija1995]

As Alan has rightly stated, Excel stores dates as numbers (01/01/1900 being 1) and then every day after that date increments the value by 1.

For future reference, you will return a #NUM ! error with the DATEDIF function when your 2nd date parameter is before​ the first date parameter.

 

[mikerickson]

Try
=SIGN(ABS(E3-I3))

 

[peterderrington]

Nope, that hasn't worked, it returns 0 in every field.

The two fields are time stamps (sorry) so for example E3= 04/08/2019 17:55:00 and I3= 04/08/2019 08:31:02

Now in theory the value in I3 SHOULD always be before the value in E3 but it wont always

 

[peterderrington]

Sorry it returns 1 not 0

 

[AlanY]

you can use the int() function to take out the time value as stored in fraction of a day, try

=if(int(E3)=int(I3),0,1)

 

[peterderrington]

So close, with a small alteration I'm almost there: =IF(INT(E3)=INT(I3), "True","False")

Now ideally I'd like to return a blank for all those values that result in a #NUM ! value originally (because there's no value in row I)

 

[AlanY]

can try

=iferror(IF(INT(E3)=INT(I3), "True","False"),"")

 

[Peter_SSs]

Another one to try

=IF(I3="","",INT(E3)=INT(I3))