date conditional formatting in xlsxwriter formatting blank cells and cells that meet criteria

Question

I have a date column I am trying to format with xlsxwriter.

The column can have a date in it or be blank.

The conditional formatting (highlight cell when date is < today) I am trying to apply is working. But it is also applying the same formatting rules to the blank cells.

How can I prevent it from formatting the blank cells?

See below for code:


format1 = workbook.add_format({'bg_color':   '#FFC7CE',
                               'font_color': '#9C0006'})
date = datetime.now()

worksheet.conditional_format(due_range, {'type':     'date',
                                        'criteria': 'less than',
                                        'value':    date,
                                        'format':   format1})

writer.save()
workbook.close()

jmcnamara jmcnamara · Accepted Answer · 2019-05-03T09:13:17

In order to use conditional formats in XlsxWriter you should first figure out what you want to do in Excel, and then transfer it over.

Dates in Excel are just numbers with a format so it looks like a blank cell is interpreted, by Excel, as 0 and thus as the 1900-01-01 epoch date.

One way to work around this is to get a conditional format range between the the first non-zero date and the date you want. In XlsxWriter you could do it like this:

import pandas as pd
from datetime import datetime, date

# Create a Pandas dataframe from some datetime data.
df = pd.DataFrame({'Dates': [date(2019, 5, 1),
                             None,
                             date(2019, 5, 2),
                             date(2019, 5, 3),
                             None,
                             date(2019, 5, 4),
                             date(2019, 5, 5)],
                   })


writer = pd.ExcelWriter("pandas_test.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

format1 = workbook.add_format({'bg_color':   '#FFC7CE',
                               'font_color': '#9C0006'})
date = datetime.now()
due_range = 'B2:B8'

worksheet.conditional_format(due_range, {'type':    'date',
                                        'criteria': 'between',
                                        'minimum':  datetime(1900, 1, 2),
                                        'maximum':  date,
                                        'format':   format1})

worksheet.set_column('B:B', 20)
writer.save()

Output:

date conditional formatting in xlsxwriter formatting blank cells and cells that meet criteria

1 Answers