If function regarding date and time

[mikeydaman]

Hey Gang,

Just a question regarding the if function.

I need to set up a tracking sheet that when you insert the date/time on cell B4 it will post "Please Review" if it has been more then 48hrs from the original date and time. I did it before but a loonnnnnngg time ago and am having a brain fart because i know you have to edit the cell to recognize the time in hh:mm... am i right on that??

Thanks all for your help.

cheers
Mike

[DonkeyOte]

You mean perhaps something along the lines of:

C4: =REPT("Please Review",(B4>0)*((NOW()-B4)>2))

[NBVC]

tRY:

=IF(B4<=NOW()-2,"PLEASE REVIEW","")

[martindwilson]

=if(a1+2>today(),","loads of time"please review") for days with a1 in format dd/mm/yyy
or for +48 hours
=IF(A1+2>NOW(),"loads of time","please review") a1 in format dd/mm/yy hh:mm

[mikeydaman]

Hey Don,

Thanks for the quick reply. I need to format the date/time cell to be in reference with the formula as it gives me a Value error.

Thanks for the help, its much appreciate.

[DonkeyOte]

Can you post a sample ?

Are you saying B4 is not a true datetime value ? Confused...

[mikeydaman]

Thanks guys for all your help NBVC.. what a **** i am huh!!! i was looking to hard into it and didn't event realize to use the NOW hahahah thought i was going to have to format the cell and all.

Thanks guys again!

[mikeydaman]

Sorry NBVC about the name

One last thing before we can close this thread. Is there a way to have the cell not have "Please Review" up all the time if their is no date in it? This is going to be saved as a template and people will just open it and use it when needed so everything will be blank.

Thanks

[DonkeyOte]

What does mine do... so confused...

=REPT("Please Review",(B4>0)*((NOW()-B4)>2))

should only display Please Review if B4 is > 0 and B4 is more than two days prior to current datetime

That said I think Martin's approach was simplest of all...

=REPT("Please Review",(B4+2)<NOW())

[NBVC]

Or simply...

=IF(OR(B4>NOW()-2,B4=""),"","PLEASE REVIEW")

[mikeydaman]

You guys are awsome thanks again.
NBVC your original was giving me a VALUE error for some reason. I though i might have had to format the date/time cell. But all in all you guys got it working for me.

Thanks again for everyones help

[mikeydaman]

Or simply...

=IF(OR(B4>NOW()-2,B4=""),"","PLEASE REVIEW")

I ended up going with this one as it worked for me

Thanks NBVC