Skip to content

Tag: date

conditional past, present, future in spreadsheets/Excel

=IF(B2=TODAY(),"Past",IF(B2<TODAY(),"Today",TEXT(B2,"ddd")))
  • Given a column B that contains dates in a sequential order.
  • Shows a “Today” when it’s today.
  • Shows a “Past” when is past.
  • Shows an abbreviated name of the day of the week.

Here is an example using emojis instead of past/today:

Easily Sortable Date and Time Representation

I was looking for a date and time representation useful for registering stock quotes in a simple plain file.

I found that the standard ISO 8601 is just the answer for this, it’s called “Data elements and interchange formats — Information interchange — Representation of dates and times”. Here is a example:

2010-01-20 22:14:38

There’s this good article from Markus Kuhn, “A summary of the international standard date and time notation”. This notation allow us to using simple lexicographical order the events.

Some examples of how to do this in Python (thanks for the Jochen Voss article “Date and Time Representation in Python”) The first for displaying the current date and time:

from time import strftime
print strftime("%Y-%m-%d %H:%M:%S")
# 2010-01-20 22:34:22

Another possibility is using strftime from datetime object.

from datetime import datetime
now = datetime.datetime.now()
print now.strftime("%Y-%m-%d %H:%M:%S")
# 2010-01-20 22:12:31

Is that. Using this notation in the begging of each line is easy to sort them in any language or using the unix sort.