Rx_
Nothing In Moderation
- Local time
- Today, 01:54
- Joined
- Oct 22, 2009
- Messages
- 2,803
If anyone has other BOX tricks / features for Excel - add it here.
That would include Improvements to my suggestions.
Rumors are that MS is doing some deal with BOX. BOX has a export utility that converts log activity to Excel. Below is the standard default Date/Time for BOX into Excel. I was recentlly called in to automate some features.
Problem: Monitor is too small
Yes, that was my problem. The end user was trying to filter by dates.
Look at the format BOX presented. On a tiny monitor.... there is a single comma between the Date and time.
OK, so the real problem is that this single Comma turns Date/Time into a Text data type that doesn't play well with Excel filtering or other VBA functions. I tried several things before noticing the silly comma.
How to get rid of the comma?
The quickest way to enable an end user is to have them highligh the Column. Then use a Search and Replace on the highlighed column.
On the search for: enter a single comma "," (don't include Quotes)
On the replace, enter a single space " "
The Date / Time will magically become understandable to Excel.
If not, set the Highlighted Column Cell properties data type to Date/Time in Excel.
Here is a code example to run once the column is highlighted.
There are two other interesting BOX tricks I can share if anyone wants them. I have deadlines today. Just send me an IM or Thanks to remind me.
That would include Improvements to my suggestions.
Rumors are that MS is doing some deal with BOX. BOX has a export utility that converts log activity to Excel. Below is the standard default Date/Time for BOX into Excel. I was recentlly called in to automate some features.
Problem: Monitor is too small
Yes, that was my problem. The end user was trying to filter by dates.
Look at the format BOX presented. On a tiny monitor.... there is a single comma between the Date and time.
OK, so the real problem is that this single Comma turns Date/Time into a Text data type that doesn't play well with Excel filtering or other VBA functions. I tried several things before noticing the silly comma.
How to get rid of the comma?
The quickest way to enable an end user is to have them highligh the Column. Then use a Search and Replace on the highlighed column.
On the search for: enter a single comma "," (don't include Quotes)
On the replace, enter a single space " "
The Date / Time will magically become understandable to Excel.
If not, set the Highlighted Column Cell properties data type to Date/Time in Excel.
Code:
[B][FONT=Arial][SIZE=2]Date / Time[/SIZE][/FONT][/B]
[FONT=Arial][SIZE=2]9/9/14, 9:15 AM[/SIZE][/FONT]
[FONT=Arial][SIZE=2]9/9/14, 9:13 AM[/SIZE][/FONT]
[FONT=Arial][SIZE=2]9/9/14, 4:15 PM[/SIZE][/FONT]
[FONT=Arial][SIZE=2]9/9/14, 4:11 PM[/SIZE][/FONT]
[FONT=Arial][SIZE=2]9/9/14, 4:10 PM[/SIZE][/FONT]
[FONT=Arial][SIZE=2]9/9/14, 4:09 PM[/SIZE][/FONT]Here is a code example to run once the column is highlighted.
Code:
Sub ConvertBoxDateTimeFromTextToDate()
' Highlight a column wiht Date / Time
' Convert Box Date / Time from Text to Date data type
Selection.Replace What:=",", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End SubThere are two other interesting BOX tricks I can share if anyone wants them. I have deadlines today. Just send me an IM or Thanks to remind me.
