Dates - Calculation AND Conditional Formating

[Solon Aquila]

Greetings,
Short Version:
I need to find a way to have the date in a cell be used for both a calculation (one date subtracted from another) AND a conditional format (Cell changes color based on the month). Cell format is mm/dd/yy

Explanation:
We enter information for each work order on a row in our Master Work Order List. For each new work order we start a new row at the bottom of the sheet.

We calculate each work order's throughput time by having a formula in column F that subtracts the Start Date in column D from the Complete Date in column E.
F#=E#-D#
All date values are entered as mm/dd/yy and the cell format is mm/dd/yy.

At the end of the month, we put an AVERAGE formula into a cell in column H and, for the sources, use all cells in column F that represent work orders that were completed for THAT month. The problem is not all work orders complete in the order they started and some can start in one month but not complete until 2 months later, so we can't just click-drag a bunch of cells in a column. Sorting doesn't work because after we click and drag a bunch of cells and get the average, when we re-sort back to Sort by Work Order Number, the clicked-cells don't move, but the data does.

We end up just looking up and down column E looking for dates from this month and hope we got them all.

So I thought, "Hey, I know, I'll put conditional formats into column F so that if the date in column E starts with 01 (January), the cell in column F will turn Red, and if it's 02 (February), the cell will turn Orange and so on through the end of the year! That way we could just look for a given color each month and are MUCH less likely to get it wrong!

I added conditional formatting using the LEFT(E#,2) formula, hit [Enter] and... nothing.

"AHA!" I thought, "I"ll set the cell formats to TEXT so the LEFT formula 'sees' the month number!"

So I set the cell formats to TEXT and then the conditional format 'sees' the '01' for January and changes color!! WOO HOO!

But now the Throughput Time formula (=E#-D#) doesn't work. ::sigh ::

I think it's because Excel can DISPLAY a date as mm/dd/yy (thanks to Date Format), but it actually calculates date information based on a number that REPRESENTS the mm/dd/yy. I read that Excel 'sees' each date as a number representing time passing, not as "mm/dd/yy" and uses that number for calculations.

SO, is there a way to have a cell's date be 'calculatable' and still have it change color based on the month?

Thank you,

Solon
PS - For forum-frequent people: My tags for this post are Date, Conditional Formatting, Calculate. Were there any others I should have selected?

 

[Peter_SSs]

Welcome to the MrExcel board!

Leave the dates in column E as Dates (ie not TEXT) and use this in the column F Conditional Formatting formula to highligh January months.

=MONTH(E#)=1

 

[Solon Aquila]

Thank you, Peter! I knew there HAD to be something simple that I was missing! I'll try it first thing when I get in. Solon

 

[Solon Aquila]

Part 2 -
OK, the MONTH command appears to work in a manner of speaking and I can make the cell change colors by switching the month in the cell. Thank you.

I find that the first conditional format is applied as long as the cell is empty, so I have a column of blank, colored cells.

I used the following formula works to make cell L53 change color based on January:
=AND((NOTBLANK(L53)),(MONTH(L53)=1))

It seems a bit excessive to have to specify 'NOTBLANK' in a cell that has no data, only a conditional format.

Have I missed something?

 

[Peter_SSs]

I used the following formula works to make cell L53 change color based on January:
=AND((NOTBLANK(L53)),(MONTH(L53)=1))

It seems a bit excessive to have to specify 'NOTBLANK' in a cell that has no data, only a conditional format.

Have I missed something?

NOTBLANK() is not a function in any version of Excel that I use.

However, you do need to make your Conditional Format check that the cell is not empty. It is like if in B1 you put the formula =A1 and A1 is empty B1 will show a 0 not 'blank'. It's just how Excel works. It is also a quirk of Excel that if working with dates it sees 0 as the date 0th January 1900.

In your CF you could use
=AND(L53>0,MONTH(L53)=1)

 

[velthro]

I'd tend to use the following
as the conditional format
=AND(L53<>"",MONTH(L53)=1)

no differance though thats the great thing about excel so many ways to accomplish the same task