Correct Form

[mtagliaferri]

Hi All,

I have a form with a field with the following calculated time:

=DateDiff("n",[DtStart],[DtEnd])\60 & ":" & DateDiff("n",[DtStart],[DtEnd]) Mod 60

This works fine, however I have an issue with the format; if the result is 5 hours 20 minutes the result will be 5:20, however if the result is 5 hours and 2 minutes then the result is 5:2.
How can I return a result for 5 hours and 2 minutes as 5:02 (even better 05:02)?

 

[theDBguy]

Hi. You may have copy and pasted the wrong thing instead of your code.

 

[mtagliaferri]

Hi. You may have copy and pasted the wrong thing instead of your code.

I was eager to hit send...…

The code is there now

 

[theDBguy]

Try:

 =Format(DateDiff("n",[DtStart],[DtEnd])\60, "00") & ":" _
    & Format(DateDiff("n",[DtStart],[DtEnd]) Mod 60, "00")

Hope it helps...

 

[mtagliaferri]

I get the following error:
The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier.

 

[theDBguy]

I get the following error:
The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier.

Hi. Try to break it down into two pieces and then put it back together once you get each piece working. For example, what do you get with just this?

Format(DateDiff("n",[DtStart],[DtEnd])\60,"00")

 

[mtagliaferri]

Thanks, I pasted separately the two parts which displayed the correct forma HH and MM, then I removed _ and it seems to be working.

=Format(DateDiff("n",[DtStart],[DtEnd])\60,"00") & ":" & Format(DateDiff("n",[DtStart],[DtEnd]) Mod 60,"00")

Thank you!

 

[theDBguy]

Thanks, I pasted separately the two parts which displayed the correct forma HH and MM, then I removed _ and it seems to be working.

=Format(DateDiff("n",[DtStart],[DtEnd])\60,"00") & ":" & Format(DateDiff("n",[DtStart],[DtEnd]) Mod 60,"00")

Thank you!

Hi. Glad to hear you got it sorted out. I wrongly used "_" because I wanted the entire code to show up on one screen without scrolling. Sorry about that.

Good luck with your project.