Problem with using Format( ) function on 1.25

[Daveo]

Hi there,

I am using the following function on a cell:

Format([cell], "hh"mm)

When the cell contains 06:00 the next day i.e. 30:00 the function
returns "01:25". When the cell contains any other time (even 29:59 and
30:01!) the function returns the correct value.

Does anyone know why this would happen?

Many thanks - David

 

[Dave Peterson]

The Format statement will return a string within your code. It won't affect the
way excel sees the value when you plop it into the cell. Excel will see that
just like you're typing it in manually.

You could use:

with activecell
.value = now
.numberformat = "hh:mm"
end with


I didn't understand what you were trying to do with:

Format([cell], "hh"mm)

Hi there,

I am using the following function on a cell:

Format([cell], "hh"mm)

When the cell contains 06:00 the next day i.e. 30:00 the function
returns "01:25". When the cell contains any other time (even 29:59 and
30:01!) the function returns the correct value.

Does anyone know why this would happen?

Many thanks - David

 

[Ron Rosenfeld]

Hi there,

I am using the following function on a cell:

Format([cell], "hh"mm)

When the cell contains 06:00 the next day i.e. 30:00 the function
returns "01:25". When the cell contains any other time (even 29:59 and
30:01!) the function returns the correct value.

Does anyone know why this would happen?

Many thanks - David

It's not entirely clear what you are doing as, to the best of my knowledge,
Excel does not have a built-in Format function and I do not believe the Format
function in VBA has an optional [cell] argument.

In addition, in the formula you write above, the "hh"mm phrase would not be
legal as a format string (no quotes around the mm) and would not give you a
separator even if the quotes were proper.

Also, 30:00 would be stored as 1.25 (assuming '.' is your decimal separator)
which is similar to 1:25.

So possibly we have here a combination of typos and a user defined function.
If so, you'll have to post that function here.

If not, you might want to try the more standard:

=TEXT(cell_ref,"hh:mm")

or some variation depending on what, exactly, you wish to see.

Or, you could just format the cell:

Format/Cells/Number Custom Type: hh:mm




--ron

 

[Daveo]

Hi there,

The 2 variables starttime and endtime take values from cells in the
sheet containing times formatted as "hh:mm"

starttime = Selection.Offset(row, column).Value
endtime = Selection.Offset(row, (column + 1)).Value

I then want to combine an employees firstname, lastname and their shift
start and end times:

shiftentry = firstname & " " & lastname & " (" & Format(starttime,
"hh:mm") & " to " & Format(endtime, "hh:mm") & ")"

I then assign this to another cell. This loops through a certain range
of my sheet and repeats for every employee for every day of the week
they work.

The code works perfectly for every time apart from when an employee
finishes (endtime) at 06:00 the next day (30:00), which I don't
understand.

Thanks - David

 

[Ron Rosenfeld]

Hi there,

The 2 variables starttime and endtime take values from cells in the
sheet containing times formatted as "hh:mm"

starttime = Selection.Offset(row, column).Value
endtime = Selection.Offset(row, (column + 1)).Value

I then want to combine an employees firstname, lastname and their shift
start and end times:

shiftentry = firstname & " " & lastname & " (" & Format(starttime,
"hh:mm") & " to " & Format(endtime, "hh:mm") & ")"

I then assign this to another cell. This loops through a certain range
of my sheet and repeats for every employee for every day of the week
they work.

The code works perfectly for every time apart from when an employee
finishes (endtime) at 06:00 the next day (30:00), which I don't
understand.

Thanks - David

My guess is that there is something funny about the data in 'endtime'.

Is that a computed value or do you enter it into the cell directly?

You might try stepping through the macro and seeing exactly what is contained
in endtime before the above code line is run.

The following works OK, so there's nothing "funny" about the FORMAT function:

=============
Sub foo()
Dim starttime
Dim endtime

starttime = [a1].Value
endtime = [a2].Value

Debug.Print Format(starttime, "hh:mm") _
& " to " & Format(endtime, "hh:mm") & ")"

End Sub
=====================


--ron

 

[Daveo]

Thanks Ron,

I've figured it out - for some unknown reason I'd declared endtime as a
string - doh!

David

 

[Ron Rosenfeld]

Thanks Ron,

I've figured it out - for some unknown reason I'd declared endtime as a
string - doh!

David

I'm glad you figured it out.
--ron