Excel Report Builder Date Formatting

(0) Share

Report

Posted on by joepurnell

735

I've built some reports in Excel Report Builder and I just can't get the dates right. When they are viewed in excel they aren't recognized as dates even if I change the cell format. I know it often doesn't apply the format change until the cell is modified so if I run text to columns and hit finish, it reformats them correctly and I can handle them as dates in my pivot table. Unfortunately, as soon as I refresh the data the format reverts. I can't modify the the line in report builder using the blue arrow like I can with other fields because its greyed out. Any suggests?

*This post is locked for comments

All responses (3)

Answers (1)

joepurnell 735 on at

Like (0)

Report

RE: Excel Report Builder Date Formatting

Thanks, Leslie. At the time I wrote this I hadn't been doing any work in SQL. Since then I've gotten my hands dirty with is so I was able to modify the connection string to include "convert(varchar(9), [Due], 1) as Due" to convert the date to a format excel will recognize without additional handling.

Thanks again!
Verified answer

L Vail 65,271 on at

Like (0)

Report

RE: Excel Report Builder Date Formatting

Hi Joe,

When Report Builder creates the view that Excel uses, it converts everything into a string. Script the view in studio manager and you'll see what I mean. My suggestion is that you modify the view and turn it back into the proper data type. This is what I do so that I don't have to deal with everything dropping in as a string.

kind regards,

Leslie
joepurnell 735 on at

Like (0)

Report

RE: Excel Report Builder Date Formatting

Bump for visibility.