24 hr date format

couldn't find 24 hr(military) date format

thanks

Just use the Format() function, like this:

Code Snippet

Format( CDate([your field]), "HH:mm:ss")

or

Code Snippet

Format( CDate([your field]), "yyyyMMdd HH:mm:ss")

Hope that helps.

|||

Thanks. How can I subtract one datetime (2007-02-02 10:11:11) to another datetime(2007-02-02 11:10:10) and put negatives in bracket(red)

thanks in advance

|||

Here is an expression you can use to evaluate how many days difference there are between two dates:

Code Snippet

=DateDiff("d", CDate(Fields!FirstDate.Value), CDate(Fields!SecondDate.Value))

To subtract a specific number of days from a date use this (just change the -1 to whatever quantity you require):

Code Snippet

=DateAdd("d", -1, CDate(Fields!MyDate.Value))

To show a negative value in red, use an IIf statement in the Color property of the cell or textbox, like this:

Code Snippet

=IIf( [my date expression] < 0, "Red", "Black")

|||Thanks again..this thing will give me just the number of days , I am trying get the out put in the format -hh:mms and then if it is negative keep it in a bracket(hh:mms)|||

Hello,

Try this to get the difference in the format you require:

=Iif(DateDiff("s", Fields!FirstDate.Value, Fields!SecondDate.Value) < 0, "(", "")

+ cStr(abs(DateDiff("h", Fields!FirstDate.Value, Fields!SecondDate.Value)))

+ ":"

+ Format(abs(DateDiff("n", Fields!FirstDate.Value, Fields!SecondDate.Value) mod 60), "00")

+ ":"

+ Format(abs(DateDiff("s", Fields!FirstDate.Value, Fields!SecondDate.Value) mod 60), "00")

+ Iif(DateDiff("s", Fields!FirstDate.Value, Fields!SecondDate.Value) < 0, ")", "")

Then, if you want the negative values to be in red, enter this in the color property expression.

=Iif(DateDiff("s", Fields!FirstDate.Value, Fields!SecondDate.Value) < 0, "Red", "Black")

This will display in red if FirstDate is after the SecondDate.

Hope this helps.

Jarret

|||

A slightly more efficient way to do it is to abstract that code into a custom function, like this:

Code Snippet

Public Function TimeDiff(firstDate As String, secondDate As String) As String

Dim timeDifference As String
timeDifference = Abs(DateDiff("h", CDate(firstDate), CDate(secondDate))) & Format(CDate(firstDate) - CDate(secondDate), ":nn:ss")

If CDate(firstDate) > CDate(secondDate) Then
timeDifference = "-(" & timeDifference & ")"
End If

TimeDiff = timeDifference
End Function

then you can just call it from your cell with an expression like this:

Code Snippet

=Code.TimeDiff(Fields!FirstDate.Value, Fields!SecondDate.Value)

Or you can simplify the previously posted code into an expression directly in the cell:

Code Snippet

=IIf(
CDate(Fields!firstDate.Value) <= CDate(Fields!secondDate.Value),
Abs(DateDiff("h", CDate(Fields!firstDate.Value), CDate(Fields!secondDate.Value))) & Format(CDate(Fields!firstDate.Value) - CDate(Fields!secondDate.Value), ":nn:ss"),
"-(" & Abs(DateDiff("h", CDate(Fields!firstDate.Value), CDate(Fields!secondDate.Value))) & Format(CDate(Fields!firstDate.Value) - CDate(Fields!secondDate.Value), ":nn:ss") & ")"
)

You have already been shown two different expressions to colour the text, and you have been given two complete correct answers by two different people - i think you have enough to finish this one off and get your report looking good