Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.
I am losing formatting in time of day and duration fields when I go to export a report to Excel. How can I get them to not change formatting when they are exported to Excel?
1. I have a time of day field set up as military time but when it exports to Excel it converts to normal time and adds AM/PM.
For example, 18:00 exports as 6:00 PM.
2. I have a duration field that is exporting differently than on the view.
For example, 2:30 exports as 2.5 hours.
QuickBase uses an unambiguous format for export purposes. This decision was made because once the data leaves the QuickBase system there is no way to tell whether 2:30 means two minutes and 30 seconds or 2 hours and 30 minutes. Whereas 2.5 hours is unambiguous. Also 18:00 could mean eighteen minutes and no seconds but 6:00 PM is unambiguously a time of day.
To control formatting you need to create formula text fields. For instance a formula text field for formatting a duration field as HH:MM would look like this:
floor([duration field]/hours(1)) & ":" & right("0" & mod(floor([duration field]/minutes(1)),60), 2)
A formula text field for formatting a time of day field into military time would have a formula like this:
hour([time of day field]) & ":" & right("0" & minute([time of day field]), 2)
These formula text fields will then export as you would like them.