Display negative number as HH:MM (1 Viewer)

[RyLane]

Hi,

I'm converting minutes to display as HH:MM but have an issue when the number is negative. Anyone know how to write this so I don't end up with a negative in front of the minutes?

Format([Variance]\60,"00") & ":" & Format(([Variance] Mod 60),"00")

i.e. -193 gives me -03:-13, whereas I would like -03:13

Thanks,

Ryan

 

[plog]

For the minutes, use absolute value: http://www.techonthenet.com/access/functions/numeric/abs.php

 

[MarkK]

Format((Abs([Variance]) Mod 60),"00")

There's also the TimeSerial() function that is worth being aware of . . .

? Format(TimeSerial(0, 193, 0), "hh:nn")

. . . but this doesn't propagate your minus sign for negative time.

 

[RyLane]

Thought we had but that turns values like 5:09 into 5:9

Still looking

 

[MarkK]

Thought we had but that turns values like 5:09 into 5:9

I don't understand this. What causes this outcome?

 

[The_Doc_Man]

1. Convert your Variance from integer seconds to a time field.

Dim VTime as Double
Dim ATime as Date

later in the code

VTime = Float( [Variance], DOUBLE )  'trust me...
ATime = CDate( VTime / 86400.0 )     'which will probably get converted to 86400#
If Atime < 0 then ATime = -ATime      'if negative, be positive about it.

When you need it, do a Format$( ATime, "hhh:mm" ) to get the time string. You MIGHT need "hhh:0m" - it always confuses me about forcing leading zeros in a time format. However, it's an easy enough experiment..

Note that you still have Variance available in its original form so could make an explicit sign in the output string by testing that variable.

 

[RyLane]

Mark,

9 is the absolute of 09

Doc,

I can't get that to work at all, doesn't like "Double" I get an expression error as soon as I put it in

 

[plog]

9 is the absolute of 09

Yes. And when you format 9 with the method you have chosen, you get 09. So what's the issue?

 

[RyLane]

Was putting the Abs() in the wrong spot. Does what it's supposed to now.

But I also want to be able to sort this value from high to low, which I guess is a whole other thing. Sorting this way just treats all values as positive.

 

[plog]

Won't sorting the field [Variance] accomplish what you want?

 

[gemma-the-husky]

maybe something like this

display =  Format(abs([Variance])\60,"00") & ":" & Format((abs([Variance]) Mod 60),"00"
 
  if variance<0 then
      display = "-" & display
 end

 

[vbaInet]

It's minutes to seconds, divided by 86400, then format:

Format(([Variance]*60)/86400, "hh:nn")

 

[RyLane]

Won't sorting the field [Variance] accomplish what you want?

When I sort it doesn't distinguish between positive and negative values. So -07:00 will appear before 08:00.

 

[plog]

What datatype is Variance? You already mentioned in your first post it contains negative values. I don't understand how it can recognize them when you are using your format function, but doesn't recognize them when you want to sort.

 

[RyLane]

What datatype is Variance? You already mentioned in your first post it contains negative values. I don't understand how it can recognize them when you are using your format function, but doesn't recognize them when you want to sort.

Variance is Decimal. It sorts properly without applying the format function but doesn't after. I don't get it either

 

[MarkK]

In a string, 9 sorts as larger than 10, because "9" is larger than "1". In a string the comparison starts at the left, not at the decimal point. And this . . .

Format(abs([Variance])\60,"00") & ":" & Format((abs([Variance]) Mod 60),"00")

. . . is a string.

 

[plog]

So my advice a few posts back stands:

Won't sorting the field [Variance] accomplish what you want?

 

[RyLane]

Yep, I just wanted to see if there was another way of doing it without bringing Variance into the mix.

 

[vbaInet]

Post #12 to include a coercion to a date type:

CVDate(Format(([Variance]*60)/86400, "hh:nn"))

You can apply a format on the above and it will sort properly. However, you should do it as plog advised. A sort can be applied on the Variance field without showing the field so I don't see why you don't want to sort on Variance. Besides, sorting on the Variance field will yield faster results than sorting on the calculated formatted field.