The datetime in salesforce is always stored in GMT (UTC). The time is corrected to the users time zone when displaying it on the UI. The time is only corrected in the UI, so you need to make the adjustment yourself for formula fields.
Datetime input field cast to another timezone
http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select_dateformats.htm
You need to adjust for your own time zone in your formula to account for this. In my formula fields, since I am in EST, I have to adjust all the datetimes by 5 hours to make sure I have the correct values. I just do this in a long case statement like below. this formula finds the hour of the day that a case was created, notice I have to adjust each time by 5 hours to make it work
TEXT(CASE(VALUE(LEFT(RIGHT(text(CreatedDate),FIND(" ", TEXT( CreatedDate ))-2),2)),
00,19,
01,20,
02,21,
03,22,
04,23,
05,00,
06,01,
07,02,
08,03,
09,04,
10,05,
11,06,
12,07,
13,08,
14,09,
15,10,
16,11,
17,12,
18,13,
19,14,
20,15,
21,16,
22,17,
23,18,
00))
Best Answer
There's the built-in Format function that is perfect for your use case:
Format(YourDate, 'dd/MM/yyyy') as StringifiedDate.Just remember that your destination data extension fields needs to be of the Text data type (the default length of 50 will be enough for this)
On a side note: I recently had to do the same thing wanting to have my dates readable by Excel and wrote an article about it and the above solution is one of a few approaches that worked.