Time Format Not Keeping in Calculation

[Dufek]

I have two fields used for the entry of this calculation.

Entry Date & Entry Time

 

I need to have the date and time in the following format in the end:

"2008-03-31T00:00:00"

 

So, to get my actual Date/Time field, I use this calculation:

Entry Date "T" Entry Time

 

Works as it should...however... the time portion does not keep the format I specify for the time entry field. On the layout, the entry field is formatted correctly 00:00:00. But when it is referenced in the calculation, it shows 0:00:00, dropping the leading zero. This is important when the time is prior to 12:00:00, such as 09:00:00. 9:00:00 is not valid. I don't know how to keep the time format in a calculation.

 

Can anyone provide some insight or suggestions to fix this?

[touchMe]

DateToText(entry date)&"T"&TimeToText(entry time)

 

Calculation output formated as text

[touchMe]

Damn! too much coffee and rushing for the phone, I pointed you in the wrong direction Dufec, sorry. I use this in some telecom stuff and here is what it should look like

 

you have source fields [date entered] and [time entered]

 

create 7 wonderful calculation fields (see below), you could compress into one calc but I like things is small bits for error checking, these are all calculation fields and all are formated as text output, I think of it as the long-hand way of doing things

 

1. hourCalc=If(Hour(time entered)

 

2. minuteCalc=If(Minute(time entered)

 

3. secondCalc=If(Seconds(time entered)

 

4. yearCalc=Year(date entered)

 

5. monthCalc=If(Month(date entered)

 

6. dateCalc=If(Day(date entered)

 

and then the magical 7th field...

 

7. Result=yearCalc &"-" & monthCalc & "-" & dateCalc & "T" & hourCalc & ":" & minuteCalc & ":" & secondCalc

[Dufek]

Spectacular! That did it!

 

Boy, that was quite a calc and solution... definitely wouldn't have come up with that one... thanks a bunch for your time and help!!

[David Head]

The simpler way to do this:

 

hourCalc=If(Hour(time entered)

 

is this:

 

Right ( "0" & Hour (time entered); 2 )

 

with a text result. So the final calculation (as touchMe said, it can be done in one calc) would be:

 

Year ( date entered ) &"-" & 
Right ( "0" & Month (date entered); 2 ) & "-" & 
Right ( "0" & Day (date entered); 2 ) & "T" & 
Right ( "0" & Hour (time entered); 2 ) & ":" & 
Right ( "0" & Minute (time entered); 2 ) & ":" & 
Right ( "0" & Seconds (time entered); 2 )

 

Or you could express it in a Let function:

 

Let ([
d = date entered; // enter fields as variables for ease of transport of calc
t = time entered;
dash = "-";
colon = ":";
yr = Year (d);
mo = Right ( "0" & Month (d); 2 ); // zero padded month number
dy = Right ( "0" & Day (d); 2 );
hr = Right ( "0" & Hour (t); 2 );
min = Right ( "0" & Minute (t); 2 );
sec = Right ( "0" & Seconds (t); 2 )
];
yr & dash & mo & dash & dy & dash & "T" & hr & colon & min & colon & sec

// date and time format returned as text in form 2008-03-09T02:08:07
)

[Dufek]

Thank you David as well.

 

Always learning something new!