• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do we display today, yesterday,tommorrow based on date

B

BK LAAL

Member
Hi All

I have a requirement

I have a date field. which will have my transaction date.
I want that date field to Display as Today instead of Todays date
iiily
I want that date field to Display as Yesterday instead of Yesterday s date
iiily
I want that date field to Display as Tommorrow instead of Tommorrows date.

This should happen in the same date field. Not using with the help of additional column
 
Hi BK LAAL

Please upload a sample file.
iiily
without sample, we have to provide answer, and then
iiily
you again have to reset accordingly.
 
yep..... i'm lost

are you saying that you have this Date field.... and you want Yesterdays - Today - Tomorrow to show up in this 1 date field (cell)?

So if the transaction date is today (1/31/14), what exactly is your desired result?
 
Hi BK LAAL,

Considering that your date field is in cell A1 and you wish to do this in excel itself, you can use the below:

=IF(TEXT(A1,"mm-dd-yy")=TEXT(NOW(),"mm-dd-yy"),"TODAY",IF(TEXT(A1,"mm-dd-yy")=TEXT(NOW()-1,"mm-dd-yy"),"YESTERDAY",TEXT(A1,"mm-dd-yy")))

Please let us all know if this works as you wished and if not please provide us with some sample data.
 
Hi BK LAAL,

Please see attached workbook.

=IF(DATE(2014,1,COLUMN()+25)=TODAY(),"TODAY",IF(DATE(2014,1,COLUMN()+25)=TODAY()-1,"YESTERDAY",IF(DATE(2014,1,COLUMN()+25)=TODAY()+1,"TOMORROW",DATE(2014,1,COLUMN()+25))))

Regards,

Peter
 

Attachments

Hi All

I would like to have same conditional formatting for all the rows of a column.
Currently conditional formatting is for the 1st field of the column. I would like it to apply it to the all cell of the columns
 
Hi BK LAAL,

Column() gives a number for the current column. So cell B1 has column() = 2, cell C1 has column() = 3 etc.
This gives us a moving count (so we can take a date and keep adding one as we move to the next column).

The +25 is to calibrate column B to start on 27 January.

In response to your next question about conditional formatting, as this is a new post on a new topic, please post it as a new query in the forum.

Regards,

Peter
 
You must log in or register to reply here.
Back
Top