SOLVED

# Dates and conditional formatting

Copper Contributor

# Dates and conditional formatting

I'm trying to create a spreadsheet that tracks books requested to be bought for customers. I need to be able to have it add a due date then flag if that date is passed as well as use a drop-down to mark status of sent/ordered/completed/unavailable so we can easily see which ones we need to chase up.

I can do the drop-down part okay but the date bit is frying my brain.  Could anyone advise, please?

2 Replies
best response confirmed by targaid (Copper Contributor)
Solution

# Re: Dates and conditional formatting

To achieve the tracking of book requests with due dates, you can use Excel's conditional formatting feature to automatically flag overdue items. Here's a step-by-step guide:

1. Column Setup:
• Column A: Customer Name
• Column B: Book Title
• Column C: Requested Date
• Column D: Due Date
• Column E: Status (Dropdown)

Step 2: Add Due Date Formula

In the "Due Date" column (Column D), you can use a formula to calculate the due date based on the requested date. Assuming you want the due date to be, for example, 14 days after the requested date, you can use the following formula in D2 and drag it down:

=IF(C2<>"", C2+14, "")

This formula adds 14 days to the requested date if it's not empty. Adjust the number of days based on your preference.

Step 3: Apply Conditional Formatting for Overdue Items

1. Select the Due Date Column (Column D):
• Click on the header of the "Due Date" column to select the entire column.
2. Conditional Formatting:
• Go to the "Home" tab.
• Click on "Conditional Formatting" in the ribbon.
• Choose "New Rule."
3. Create a Rule for Overdue Items:
• In the "Select a Rule Type" box, choose "Use a formula to determine which cells to format."
• Enter the following formula:

=AND(D2<>"" , D2<TODAY())

This formula checks if the due date is not empty and if it's earlier than today.

• Click on "Format" to choose the formatting style for overdue items (e.g., red fill).
1. Apply Formatting and Close:
• Click "OK" to apply the formatting.
• Click "OK" again to close the Conditional Formatting Rules Manager.

Now, the due date column will be formatted to highlight overdue items automatically.

Step 4: Set Up Dropdown for Status

For the "Status" column (Column E), you can use Data Validation to create a dropdown list. Here's how:

1. Select the Status Column (Column E):
• Click on the header of the "Status" column to select the entire column.
2. Data Validation:
• Go to the "Data" tab.
• Click on "Data Validation."
• In the "Allow" box, choose "List."
• In the "Source" box, enter your status options (e.g., Sent, Ordered, Completed, Unavailable), separated by commas.
• Click "OK" to apply the dropdown.

Now, you can easily choose the status from the dropdown for each book request.

With these steps, you have a spreadsheet that calculates due dates based on requested dates, flags overdue items using conditional formatting, and allows you to track the status of each book request using a dropdown list. Adjust the formulas and formatting as needed based on your specific requirements.The text/steps was revised with the AI.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

# Re: Dates and conditional formatting

Thanks so much for that. Wasn't expecting quite such an in-depth response. I'll have fun adapting it and learning those routines to use elsewhere.

I really appreciate it.
1 best response

Accepted Solutions
best response confirmed by targaid (Copper Contributor)
Solution

# Re: Dates and conditional formatting

To achieve the tracking of book requests with due dates, you can use Excel's conditional formatting feature to automatically flag overdue items. Here's a step-by-step guide:

1. Column Setup:
• Column A: Customer Name
• Column B: Book Title
• Column C: Requested Date
• Column D: Due Date
• Column E: Status (Dropdown)

Step 2: Add Due Date Formula

In the "Due Date" column (Column D), you can use a formula to calculate the due date based on the requested date. Assuming you want the due date to be, for example, 14 days after the requested date, you can use the following formula in D2 and drag it down:

=IF(C2<>"", C2+14, "")

This formula adds 14 days to the requested date if it's not empty. Adjust the number of days based on your preference.

Step 3: Apply Conditional Formatting for Overdue Items

1. Select the Due Date Column (Column D):
• Click on the header of the "Due Date" column to select the entire column.
2. Conditional Formatting:
• Go to the "Home" tab.
• Click on "Conditional Formatting" in the ribbon.
• Choose "New Rule."
3. Create a Rule for Overdue Items:
• In the "Select a Rule Type" box, choose "Use a formula to determine which cells to format."
• Enter the following formula:

=AND(D2<>"" , D2<TODAY())

This formula checks if the due date is not empty and if it's earlier than today.

• Click on "Format" to choose the formatting style for overdue items (e.g., red fill).
1. Apply Formatting and Close:
• Click "OK" to apply the formatting.
• Click "OK" again to close the Conditional Formatting Rules Manager.

Now, the due date column will be formatted to highlight overdue items automatically.

Step 4: Set Up Dropdown for Status

For the "Status" column (Column E), you can use Data Validation to create a dropdown list. Here's how:

1. Select the Status Column (Column E):
• Click on the header of the "Status" column to select the entire column.
2. Data Validation:
• Go to the "Data" tab.
• Click on "Data Validation."
• In the "Allow" box, choose "List."
• In the "Source" box, enter your status options (e.g., Sent, Ordered, Completed, Unavailable), separated by commas.
• Click "OK" to apply the dropdown.

Now, you can easily choose the status from the dropdown for each book request.

With these steps, you have a spreadsheet that calculates due dates based on requested dates, flags overdue items using conditional formatting, and allows you to track the status of each book request using a dropdown list. Adjust the formulas and formatting as needed based on your specific requirements.The text/steps was revised with the AI.