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

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

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

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

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

as the time is entered, convert it to a timeserial. Accumulate your
durations in a date variable and write the sum to the cumulative label as
you go. Don't put back the cumulative label and try to convert it and then
add the newly entered value.

You know what you are doing better than I, but the solution is to get to and
stay in timerserials for calculations.


--
Regards,
Tom Ogilvy


"Graham Y" wrote in message
...
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

Thanks, Now solved by the use of a static variable, to keep the running total
in.

"Tom Ogilvy" wrote:

as the time is entered, convert it to a timeserial. Accumulate your
durations in a date variable and write the sum to the cumulative label as
you go. Don't put back the cumulative label and try to convert it and then
add the newly entered value.

You know what you are doing better than I, but the solution is to get to and
stay in timerserials for calculations.


--
Regards,
Tom Ogilvy


"Graham Y" wrote in message
...
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