The data is only written to the sheet once the whole weeks times have been
entered on the form, as I have to split the hours depending upon time of day
for differing pay rates. I have potentially 7 start times and 7 end times, as
the end time is entered, I check for: a start time, min 8 hour shift (if less
then adjust end time so it is an 8 hour shift), max 14 hour shift (highlight
textbox, but accept value) then display duration of shift in a label next to
end time. At the bottom of the times is a running total, again in a label, so
the data is only on the form as text. So I have 7 labels which either contain
vbNullString or a string in the format hh:mm, I an use cDate to get time
values, but when it goes over 24 hours the result ends up as 31/12/1899
12:00:00 and format [h]:mm fails

"Tom Ogilvy" wrote:

Aren't your source values in cells in the worksheet stored as time values?

Why are you then using strings?

myTime = Application.Text(application.Sum(Range("A1:A2")),"[h]:mm")

In any event, do your math with the timeserial numbers. Only use strings
for display purposes.

--
Regards,
Tom Ogilvy

"Graham Y" wrote in message
...
Thanks Bob it works whilst the running total is under 24 hours, but when I
get to say "30:00" + "10:00:00" it fails type mismatch any ideas?

"Bob Phillips" wrote:

In VBA you have to use the worksheet TEXT function

myTime = Application.Text(d1 + d2, "[h]:mm")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Graham Y" wrote in message
...
Hi
On a timesheet form I collect start and end times, I want to display
the
hours against each day (no problems) and I want to show the running
total,
for visual error checking. In a cell I would use the format [h]:mm but
this
doesn't work in VBA, d1 & d2 = "18:00:00" format(d1+d2,"[h]:mm") gives
:12
take out the [] and I get 31/12/1899 12:00:00 and what I want is to
display
36:00 in a lable
I've been trying to convert the value in the lable, but I can't do
Csng(lbl)
the function won't convert a String to a Single. How can I get around
this?
Cdate() checks for a valid date format and then does a conversion, so
if
the
hours are <24
if d1="18:00" I can do x= Csng(Cdate(d1)) and get x=18 but it doesn't
work
if d1="36:00"
I'm open to alternative suggestions, but I'd rather not have to write
an
interim value back to the spreadsheet, just seems clumsy.
TIA