DATE and TIME FORMATTING in Office 2007

[Opv]

I have a simple worksheet which includes a series of blood pressure readings. The date and time of each reading is recorded in column A and appear. All of the entries appears in the following format (configured using the Custom format):

MM/DD/YYYY HH:MM AM/PM
07/1/2017 10:01 AM

However, when I test the cells in column A using the ISTEXT function, I find that some of the cells return TRUE and others return FALSE. I suspect that might be the reason some of my average formulas are returning inconsistent averages.

What are my options for converting the existing dates to the proper format without losing the desired custom format?

[Kenneth Hobs]

It depends on your data I guess. 07/1/2017 10:01 AM should have been 07/01/2017 10:01 AM.

It might be best to use VBA and try using CDate() and then theRange.NumberFormat(). CDate() will likely fail which would then require a parsing routine using Split().

[Opv]

It depends on your data I guess. 07/1/2017 10:01 AM should have been 07/01/2017 10:01 AM.


Thanks. Actually, you are correct. I typed it incorrectly in my original inquiry. The date does in reality reflect 07/01/2017 10:01 AM. Sorry about that.

I'll try your suggestion. Thanks.

[p45cal]

How are you getting the data into the spreadsheet?
Excel tries to be helpful with dates and that help can be a pain when the dates are not in the expected format (months and days get muxed ip).
There are ways of getting it in correctly depending on how the data is coming into Excel.

[Opv]

How are you getting the data into the spreadsheet?

Thanks. I think that was the source of my problem. The rows containing the inconsistent format were copied and pasted from a PDF document I had created from one of my backups. Based on Kenneth's reply I was able to get all of the date fields in a consistent format. I'm still auditing some cells to see what might be causing a slight anomaly in several of my summary formulas in which I average BP readings by month. I seem to be getting different averages for one particular month when using AVERAGEIFS as compared to a manual AVERAGE for the days in that particular month. I've gone through the relevant cells several times and made sure all of the text-formatted data fields were converted to numeric. I'll hopefully figure it out eventually once I get over this brain fog.

[p45cal]

It all hinges around preventing Excel from being 'helpful' at the pasting stage.
Make sure that the cells which receive the data are formatted as TEXT before you paste anything into them. Best start with a newly created sheet too.
After that you can control how the dates are interpreted. You might already be doing a Text-to-columns, in which case for the column containing dates you should be able tell Excel how to interpret them (note that here, you're telling Excel how the dates are coming in, not how you want them to be - that comes later).
21121
It's very difficult to advise definitively since there are so many unknowns. Provide a sample of pdf file with dates prone to being misinterpreted and I can advise much better.

[Opv]

It all hinges around preventing Excel from being 'helpful' at the pasting stage.
Make sure that the cells which receive the data are formatted as TEXT before you paste anything into them. Best start with a newly created sheet too.
After that you can control how the dates are interpreted. You might already be doing a Text-to-columns, in which case for the column containing dates you should be able tell Excel how to interpret them (note that here, you're telling Excel how the dates are coming in, not how you want them to be - that comes later).
...
It's very difficult to advise definitively since there are so many unknowns. Provide a sample of pdf file with dates prone to being misinterpreted and I can advise much better.

Thanks, I'll keep that in mind in the event I need to copy/paste from PDF in the future. As best I can tell I've been able to get all of the data columns in the proper format. (For what it's worth, the PDF backup files were created from within EXCEL by simply printing the document using the system's built-in Print-to-PDF functionality.)

[Opv]

I have resolved the formatting issue, thanks to the input of those who replied to my original post. The anomalies I mentioned about manually averaging the data for a particular month as opposed to using AVERAGEIFS turned out to be caused not by formatting but because the date column is formatted to include both the DATE and the TIME, thus resulting in a value with numbers after the decimal point. Consequently, when my AVERAGEIFs formula tested for dates that were >= the first day of a particular month AND <= the last day of that particular month, the last day of the month is not getting factored into the equation, as there is always a fractional number after the decimal point which causes that date to be > than the last day of the month. I have tried using INT, ROUNDDOWN and even VALUE(LEFT(DATE CELL, 5)) and those functions do not appear to be allowed in conjunction with AVERAGEIFS, even when I use CTRL-SHFT-ENTER. Is there a solution to this dilemma without having to place the times in a separate column?

[SamT]

DbleVar \ 1 Returns a number = DbleVar with the decimal part Truncated, ie not Rounded Like CInt does. (NOT "/")

Therefore, something like (in VBA)

Cell = Cell \ 1

However, VBA has the DateValue Function

Cell = DateValue(Cell)


>= the first day of a particular month AND <= the last day of that particular month
In VBA, and I think in Excel Formulas
'Assumes Both Reference Dates are in Date only numbers, ie Integers
=> FirstDayDate AND < LastDayDate + 1
LastDayDate + 1 = (MM+1)/01/2017 00:00:00

[Opv]

Thanks. I just decided to go ahead and include a helper column to extract the dates from the dates/times and key off that column.