Ask Your Question

I want to flag up overdue subscriptions

asked 2018-11-21 20:07:04 +0100

petty cash gravatar image

I want to flag up overdue subscriptions where date last paid < today's date -365. Conditional formatting? Can I use date format dd/mm/yy?

edit retag flag offensive close merge delete


Thge only good date format is YYYY-MM-DD as standardised under ISO 8601.

Lupp gravatar imageLupp ( 2018-11-21 20:21:07 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-11-22 01:28:58 +0100

erAck gravatar image

The condition can be expressed as A1 < TODAY() - 365 where A1 is the cell containing the date last paid. This formula expression can be entered either as a formula cell (returning TRUE or FALSE) or as formula in a conditional format.

edit flag offensive delete link more

answered 2018-11-21 20:20:01 +0100

Lupp gravatar image

updated 2018-11-21 20:23:03 +0100

Dates are by default represented by integer numbers in spreadsheets. It's this kind of dates that can be formatted in different ways. The respective cells answer TRUE if asked =NUMBER(myCellReference).
If you have dates represented by numbers, the currently used formats are of no meaning in any calculation or comparison.

If you entered (imported) dates in a way preventing the conversion into numbers, you can only compare for equality with dates also given as texts and in the same format.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-11-21 20:07:04 +0100

Seen: 50 times

Last updated: Nov 22 '18