Format Cells to show different values

[bony_tony]

I currently have this format for my cells in a list - "In "#,##0"
Day(s)";#,##0" Day(s) ago" I would like to be able to show the text
"Today" if the cell value is zero. Also, if possible, I'd like to get
rid of the "(s)" ie if the value is more than 1 or less than -1 it
should say "Days" and say "Day" if equal to 1 or -1.

Also, can someone point me in the direction of a help file/website for
advanced formatting like this..

Thanks
Tony

 

[Guest]

I don't think you'll be able to get rid of the (s) just with formatting, you
don't get enough options, but you can add a condition for zero just by
changing to

"In "#,##0" Day(s)";#,##0" Day(s) ago";"Today"

 

[Roger Govier]

Hi Tony

There is no way with cell formatting to include or exclude the (s) if
periods greater than 1 day are involved.
You could achieve your result with formulae though.
If you used a helper column which calculated the value and hid this
column, then you could use the following formula to achieve what you
want.
In my case I assumed column A had the calculated days. Adjust to suit.

=IF(A1=0,"Today",
IF(A1>0,"In "&ROUNDUP(A1,0)&" day"&IF(A1>1,"s",""),
IF(A1<0,ABS(ROUNDUP(A1,0))&" Day"&IF(A1<-1,"s","")&" ago")))

If you are using the values in further calculations, then obviously this
would have to refer to the hidden column, not your formatted column.