Convert date and time into Diffrent text.

Hi,Team

I have an data in sheet 1 , I Have mentioned 3 remark in col B
1) BACKDATED SO --- if date is backdated
2) SO RECEIVED BEFORE 3:00 PM ------if date is today but if it is before 3:00 PM
3) SO RECEIVED AFTER 3:00 PM--------if date is today but if it is after 3:00 PM

I am looking for formula any help very much appriciate.
I have attached an example sheet.

In B2

=IF(INT(A2)=TODAY(),IF(A2-INT(A2)<15/24,"SO RECEIVED BEFORE 3:00 PM","SO RECEIVED AFTER 3:00 PM"),"BACKDATED SO")

Copy down

=Int(a1) anf format as date gives you the date

=MOD(A1,1) and format as time gives you the time.

So using some "if" you get your result.

hi visha. do adjust the formula accordingly to accommodate 3 pm. the formula Ace_XL provided would go through 2 tests;
1) whether the date in Column A is today
2) whether the time in Column A is less than 3pm

so today's date at 3 pm will pass the first test but fail the 2nd. hence categorized that as AFTER 3pm. it depends on what you want. if it's supposed to be together with BEFORE 3pm, then:
=IF(INT(A2)=TODAY(),IF(A2-INT(A2)<=15/24,"SO RECEIVED BEFORE 3:00 PM","SO RECEIVED AFTER 3:00 PM"),"BACKDATED SO")

another

Formula:

Please Login or Register  to view this content.

Thanks u very much Guys all the formula works fine.
Good day