Display 600 without the zeros

[k9mikep]

I was trying to figure out a custom cell format to make only the 6 appear when the answer is 600 or 4 when the answer is 400.

AA9=1700
X9=1100

AA9-X9=600 ----> 6 (representing six hours)

AA9 & X9 represent military times

Thanks

[Palmetto]

Assuming you are using valid time values, just format the cell as [h] and it will display just the hour portion of the result.

[protonLeah]

How about:

Please Login or Register  to view this content.

[k9mikep]

it doesnt seem to be working..i may just be way off but heres the situation.

in cell X9 the user inserts what time they started their shift, ex: X9=0500 (represents 5am military format)
in cell AA9 the user inserts the time their shift ended, ex AA9=1100 (rep 11am mil format
in the total hours cell AD9, i want it to display how many hours that person worked but display a blank if there is no value

The formula that I have in there now is:

AD9=IF((0&X9)+(0&AA9)=0,"",TEXT((0&AA9)-(0&X9),"h")) and I get AD=0

X9 ,AA9, AD9 all formated as text

[shg]

What if the elapsed time were 6:30, or 6:07? How should that be displayed?

[k9mikep]

It should be displayed as such: 0530 or 0607....Now we require that the user rounds to the nearest quarter hour...so 0607 would be 0615, and so on....

Also, i just though of this, If someone inputs the start time as 0500 and their finish time as 1130 can I get the total hrs to display 6.5?

X9=0500, AA9=1130 AD=6.5?

[shg]

Maybe =DOLLARFR(ROUND(DOLLARDE((AA9 - X9) / 100, 60) * 4, 0) / 4, 60)

[k9mikep]

that didnt work, it wont do the calc's or even hide the formula

[Palmetto]

that didnt work, it wont do the calc's or even hide the formula

Just covering all bases here: are you certain that the time values are IN FACT valid time entries? If you are using the default horizontal cell alignment, then the values will the right-aligned (wide the cell if necessary to see the effect).

If you don't have valid time values you will need to convert them from text to numbers using the PasteSpecial multiply (x 1) or add (+ 0) option.

[protonLeah]

Please Login or Register  to view this content.