AutoFiltering Date/Time difficulties

[Greasy]

I have a database that contains a column with data in the format "m/d/yy h:mm:ss;@". Sometimes the time is N/A due to user input, which is necessary for this application because there is not always a time associated with the date. I have to have the time and date in the same cell, they cannot be in separate cells due to the way they need to be filtered for printing.

When I try to use AutoFilter with the above format, it from left to right on the tree for all values that do not contain N/A the way it should, but then any days that have the time as N/A are listed at the bottom as "4/1/13 N/A" and "4/2/13 N/A" etc. So the filter tree works like this: Filter year Filter month Filter day Filter hours Filter minutes then at the bottom the dates with N/A are listed and cannot be expanded or anything. Basically I want the filter to be: Filter year Filter month Filter day Filter hours AndOr N/A If hours Then Filter minutes.

Is this possible or do I need to go about this another way?

This database is a list of events that are entered via a userform, I want to be able to filter through several hundred events by date and time, so that every day a report can be printed containing events from 0500hrs the day before to 0500hrs current day. I can do this now by manually choosing the right filters and by having the date and time in separate cells but I want to automate it as much as possible.

Thanks in advance for the help, sorry it's so long...

 

[Andrew Poulsom]

If there is no time why don't you omit it rather than putting N/A? A value like '4/2/13 N/A' is text, not a serial date.

 

[Greasy]

That might work, good idea. The only problem I see is that I will have to do some kind of conditional formatting. If the format remains m/d/yy h:mm:ss;@ and have a blank time the time is automatically entered as 00:00:00 which means it could be included or not included in a printed report once filtering is done from one time to another. Is there a way to avoid that? It wouldn't hurt to have it in the report either way, as long as it isn't missing from a report, so I could just make the format be conditional and change to m/d/yy if there is no time value.

Is that the best way? If so, can you give me an example of vba formatting cells like that?

For example starting at the UserForm code,

'textbox1 is where time would be entered
'texbox2 is where date would be entered

If TextBox1.Value = "N/A" Then
    Sheets("Sheet1").Range("Cell1").Format 'date only 
    Else:
        [/COLOR][COLOR=#333333]Sheets("Sheet1").Range("Cell1").Format 'date and time[/COLOR][COLOR=#333333]
End If

Sheets("Sheet1").Range("Cell1").Value = TextBox2 'date

I'm not sure how to format a cell with VBA. Also, my current userform set up has 3 boxes for date time entry: one textbox for time, one combobox with selections for month/year for example "March '13" and one for day in the form of "1" or "31". How can I put all of these together into a cell in the format of mm/dd/yyyy and if applicable h:mm;@?

Thanks again for the help!

 

[Andrew Poulsom]

I don't think that the format matters, but the macro recorder will give you the formatting code. You should ensure that all your dates are serial dates. Remember that a TextBox contains text. I would use three ComboBoxes for the date - Day, Month and Year. Then you can use code like this:

ActiveCell.Value = DateSerial(cboYear.Value, cboMonth.ListIndex + 1, cboDay.Value) + TimeValue(TextBox1.Text)

 

[Greasy]

Thank you very much for the help. I'm not familiar with DateSerial so I will research it. It looks like it will work better than what I was planning, which was to use a number format for each combobox for the date, then format the destination cell, then put the values into the cell along with adding a "/" between all of them.