Discussion on:

Convert and format values into decimal values that Excel can interpret as time

I use a spreadsheet to keep track of a set of items received for review, in this workbook I have sheets to show what is due per month--one for each month, a summary sheet of what was received by category and which group worked the item, a sheet used to calculate the number of items received per month along with the received date and due dates calculating time between, a sheet to keep track of all receipts per month and by due date which feeds the summary sheet graphs. The data is copied from an Outlook Task (start dates) [copy and paste--to directly export from Outlook copies too many unneeded and unused data points]. Which in turn would still require filtering, it is much easier to copy and paste. One of my co-workers set up a workaround for me to enable a correct count of items received per month. He setup the following formulat to convert each date to text: TEXT(INDIRECT("'Processing Time'!C"&$D25+1),"mm/dd/yyyy") where processing time is the name of the sheet where the information is found. This enables a countif function to correctly count the number of items with a certain month. Without this extra step the count is incorrect (for the month of September, for example the countif alone counts 28, however forcing each to be text the count is 35) This way I don't have to go into each entry and ensure it is formatted as text. Sometimes when copying the whole date (e.g. Tue Nov 1, 2011) it would copy as text with an appostrophe and sometimes I would have to add one when I deleted the day of the week leaving the numerical date) With the workaround I don't have to worry about what format it is in, the subtraction works either format or mixed formats, it is just the countif function that has problems.