302 Found

Found

The document has moved here.

Knowledge Base

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.

Comments

Post new comment

The content of this field is kept private and will not be shown publicly.
Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.
302 Found

Found

The document has moved here.