I need the time to display in the 'hh:mm AM/PM' format.

I've written a formula that pulls the time from a standard Date/Time field and converts GMT to EST while taking daylight savings time into account.

IF(DATEVALUE(Date_Time__c) <= DATE(2011,03,12) || DATEVALUE(Date_Time__c) >= DATE(2011,11,06),

/* checks to see if date falls between Daylight Savings adjustment dates (would have to be updated once a year) */

TEXT(IF(VALUE(MID(TEXT(Date_Time__c),12,2)) <= 17, VALUE(MID(TEXT(Date_Time__c),12,2)) - 5, VALUE(MID(TEXT(Date_Time__c),12,2)) - 17)),

TEXT(IF(VALUE(MID(TEXT(Date_Time__c),12,2)) <= 16, VALUE(MID(TEXT(Date_Time__c),12,2)) - 4, VALUE(MID(TEXT(Date_Time__c),12,2)) - 16)))

/* adjusts hours for EST & Daylight Savings according to date */

&

MID(TEXT(Date_Time__c),14,3)

/* displays minutes */

&

IF(VALUE(MID(TEXT(Date_Time__c),12,2)) <= 16, " AM", " PM")

/* checks GMT time and adds 'AM' or 'PM' based on results */

When I try to add the conditional "0" on the front end (using LPAD) I get the 'too big to execute' error we're all so familiar with.

I've already tried putting the hour calculations in a separate field and referencing that hidden field in the IF statement for the conditional "0". No dice.

Anyone have an idea of how to do this more simply? Thanks in advance.

-Leonard