Hi...Im back again.............I thought i'd sorted it then this one popped
up........

I'm doing the time sheets for work (still!).........to try and keep it
simple, I input peoples start and finish times into a cell, that data is
copied to a section lower down the page (this is eventually printed and
handed out so that the staff know who is doing what that week)
Well, it was working fine....till i tried something..

The cells have the staff members name......their start time,their finish
time...the finish time minus the start time gives the hours a person works
that day.......so far so good......
If a person is off work then its displays DAY in the start time box....and
OFF in the end time box - this makes it show #value! in the Hours
box.......this I can sort of live with....but......the worksheet also adds
up the persons hours.......but what with this #value
in one of the cells - it is stopping it from adding up.

If we had permanent static duties it wouldnt be a problem - but every ow and
again a person works extra.

How can I make an entry that will allow this ?

All help appreciated - Dave




MONDAY DAY OFF #VALUE!
TUESDAY 08:20 17:30 9:10
WEDNESDAY 08:50 18:20 9:30
THURSDAY 08:50 12:40 3:50
FRIDAY 08:50 17:35 8:45
SATURDAY 08:50 17:35 8:45

TOTAL HOURS #VALUE!



I can email the worksheet if you want it

don't know what your layout is, but if the day, start time, end time and hours
worked are adjacent, you could use a formula for the hours worked.

if(c1="OFF",0,c1-b1)

--


Gary


"DB" <(E-Mail Removed)> wrote in message
news:eRWlh.23843$(E-Mail Removed)...
> Hi...Im back again.............I thought i'd sorted it then this one popped
> up........
>
> I'm doing the time sheets for work (still!).........to try and keep it simple,
> I input peoples start and finish times into a cell, that data is
> copied to a section lower down the page (this is eventually printed and handed
> out so that the staff know who is doing what that week)
> Well, it was working fine....till i tried something..
>
> The cells have the staff members name......their start time,their finish
> time...the finish time minus the start time gives the hours a person works
> that day.......so far so good......
> If a person is off work then its displays DAY in the start time box....and OFF
> in the end time box - this makes it show #value! in the Hours box.......this I
> can sort of live with....but......the worksheet also adds up the persons
> hours.......but what with this #value
> in one of the cells - it is stopping it from adding up.
>
> If we had permanent static duties it wouldnt be a problem - but every ow and
> again a person works extra.
>
> How can I make an entry that will allow this ?
>
> All help appreciated - Dave
>
>
>
>
> MONDAY DAY OFF #VALUE!
> TUESDAY 08:20 17:30 9:10
> WEDNESDAY 08:50 18:20 9:30
> THURSDAY 08:50 12:40 3:50
> FRIDAY 08:50 17:35 8:45
> SATURDAY 08:50 17:35 8:45
>
> TOTAL HOURS #VALUE!
>
>
>
> I can email the worksheet if you want it
>

If they have the day off, do you want 0 hours to appear?

You could use IsError inside of an IF statement to trap the error.
=IF(ISERROR(B1-A1),0,B1-A1)
where B1 is the end time and A1 is then start time. Substitute B1-A1 for
whatever formula you are using if it is different.


"DB" wrote:

> Hi...Im back again.............I thought i'd sorted it then this one popped
> up........
>
> I'm doing the time sheets for work (still!).........to try and keep it
> simple, I input peoples start and finish times into a cell, that data is
> copied to a section lower down the page (this is eventually printed and
> handed out so that the staff know who is doing what that week)
> Well, it was working fine....till i tried something..
>
> The cells have the staff members name......their start time,their finish
> time...the finish time minus the start time gives the hours a person works
> that day.......so far so good......
> If a person is off work then its displays DAY in the start time box....and
> OFF in the end time box - this makes it show #value! in the Hours
> box.......this I can sort of live with....but......the worksheet also adds
> up the persons hours.......but what with this #value
> in one of the cells - it is stopping it from adding up.
>
> If we had permanent static duties it wouldnt be a problem - but every ow and
> again a person works extra.
>
> How can I make an entry that will allow this ?
>
> All help appreciated - Dave
>
>
>
>
> MONDAY DAY OFF #VALUE!
> TUESDAY 08:20 17:30 9:10
> WEDNESDAY 08:50 18:20 9:30
> THURSDAY 08:50 12:40 3:50
> FRIDAY 08:50 17:35 8:45
> SATURDAY 08:50 17:35 8:45
>
> TOTAL HOURS #VALUE!
>
>
>
> I can email the worksheet if you want it
>
>
>

er............this is where I show my total ignorance.....

I havent a clue about VB and do everything in crude methods....such as
working the maths out myself and then using the autosum to get my answers in
the way that I work them out.(If you follow me)

If you have the time, tell me what to do where and i'll do it..

Thanks

Dave

P.S. it's 23.15 here so Happy New Year to everyone



"JMB" <(E-Mail Removed)> wrote in message
news:A72F79CE-756D-4E72-B923-(E-Mail Removed)...
> If they have the day off, do you want 0 hours to appear?
>
> You could use IsError inside of an IF statement to trap the error.
> =IF(ISERROR(B1-A1),0,B1-A1)
> where B1 is the end time and A1 is then start time. Substitute B1-A1 for
> whatever formula you are using if it is different.
>
>
> "DB" wrote:
>
>> Hi...Im back again.............I thought i'd sorted it then this one
>> popped
>> up........
>>
>> I'm doing the time sheets for work (still!).........to try and keep it
>> simple, I input peoples start and finish times into a cell, that data is
>> copied to a section lower down the page (this is eventually printed and
>> handed out so that the staff know who is doing what that week)
>> Well, it was working fine....till i tried something..
>>
>> The cells have the staff members name......their start time,their finish
>> time...the finish time minus the start time gives the hours a person
>> works
>> that day.......so far so good......
>> If a person is off work then its displays DAY in the start time
>> box....and
>> OFF in the end time box - this makes it show #value! in the Hours
>> box.......this I can sort of live with....but......the worksheet also
>> adds
>> up the persons hours.......but what with this #value
>> in one of the cells - it is stopping it from adding up.
>>
>> If we had permanent static duties it wouldnt be a problem - but every ow
>> and
>> again a person works extra.
>>
>> How can I make an entry that will allow this ?
>>
>> All help appreciated - Dave
>>
>>
>>
>>
>> MONDAY DAY OFF #VALUE!
>> TUESDAY 08:20 17:30 9:10
>> WEDNESDAY 08:50 18:20 9:30
>> THURSDAY 08:50 12:40 3:50
>> FRIDAY 08:50 17:35 8:45
>> SATURDAY 08:50 17:35 8:45
>>
>> TOTAL HOURS #VALUE!
>>
>>
>>
>> I can email the worksheet if you want it
>>
>>
>>

> I havent a clue about VB
There were instructions to use VB at all. The answers were both for
formulas used in the worksheet - to replace the ones returning the #Value
error.

Instead of using

=B1-A1 to get the number of hours worked, use

=IF(ISERROR(B1-A1),0,B1-A1)

obviously adjust the B1 to indicate the cell in the row that has the finish
time and A1 to indicate the cell in the row that has the start time.


--
Regards,
Tom Ogilvy

"DB" <(E-Mail Removed)> wrote in message
news:4yXlh.23859$(E-Mail Removed)...
> er............this is where I show my total ignorance.....
>
> I havent a clue about VB and do everything in crude methods....such as
> working the maths out myself and then using the autosum to get my answers
> in the way that I work them out.(If you follow me)
>
> If you have the time, tell me what to do where and i'll do it..
>
> Thanks
>
> Dave
>
> P.S. it's 23.15 here so Happy New Year to everyone
>
>
>
> "JMB" <(E-Mail Removed)> wrote in message
> news:A72F79CE-756D-4E72-B923-(E-Mail Removed)...
>> If they have the day off, do you want 0 hours to appear?
>>
>> You could use IsError inside of an IF statement to trap the error.
>> =IF(ISERROR(B1-A1),0,B1-A1)
>> where B1 is the end time and A1 is then start time. Substitute B1-A1 for
>> whatever formula you are using if it is different.
>>
>>
>> "DB" wrote:
>>
>>> Hi...Im back again.............I thought i'd sorted it then this one
>>> popped
>>> up........
>>>
>>> I'm doing the time sheets for work (still!).........to try and keep it
>>> simple, I input peoples start and finish times into a cell, that data is
>>> copied to a section lower down the page (this is eventually printed and
>>> handed out so that the staff know who is doing what that week)
>>> Well, it was working fine....till i tried something..
>>>
>>> The cells have the staff members name......their start time,their finish
>>> time...the finish time minus the start time gives the hours a person
>>> works
>>> that day.......so far so good......
>>> If a person is off work then its displays DAY in the start time
>>> box....and
>>> OFF in the end time box - this makes it show #value! in the Hours
>>> box.......this I can sort of live with....but......the worksheet also
>>> adds
>>> up the persons hours.......but what with this #value
>>> in one of the cells - it is stopping it from adding up.
>>>
>>> If we had permanent static duties it wouldnt be a problem - but every ow
>>> and
>>> again a person works extra.
>>>
>>> How can I make an entry that will allow this ?
>>>
>>> All help appreciated - Dave
>>>
>>>
>>>
>>>
>>> MONDAY DAY OFF #VALUE!
>>> TUESDAY 08:20 17:30 9:10
>>> WEDNESDAY 08:50 18:20 9:30
>>> THURSDAY 08:50 12:40 3:50
>>> FRIDAY 08:50 17:35 8:45
>>> SATURDAY 08:50 17:35 8:45
>>>
>>> TOTAL HOURS #VALUE!
>>>
>>>
>>>
>>> I can email the worksheet if you want it
>>>
>>>
>>>
>
>

Absolutely Spot On - Problem Sorted....

Many Many Thanks

Dave ))






"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>> I havent a clue about VB
> There were instructions to use VB at all. The answers were both for
> formulas used in the worksheet - to replace the ones returning the #Value
> error.
>
> Instead of using
>
> =B1-A1 to get the number of hours worked, use
>
> =IF(ISERROR(B1-A1),0,B1-A1)
>
> obviously adjust the B1 to indicate the cell in the row that has the
> finish time and A1 to indicate the cell in the row that has the start
> time.
>
>
> --
> Regards,
> Tom Ogilvy
>
> "DB" <(E-Mail Removed)> wrote in message
> news:4yXlh.23859$(E-Mail Removed)...
>> er............this is where I show my total ignorance.....
>>
>> I havent a clue about VB and do everything in crude methods....such as
>> working the maths out myself and then using the autosum to get my answers
>> in the way that I work them out.(If you follow me)
>>
>> If you have the time, tell me what to do where and i'll do it..
>>
>> Thanks
>>
>> Dave
>>
>> P.S. it's 23.15 here so Happy New Year to everyone
>>
>>
>>
>> "JMB" <(E-Mail Removed)> wrote in message
>> news:A72F79CE-756D-4E72-B923-(E-Mail Removed)...
>>> If they have the day off, do you want 0 hours to appear?
>>>
>>> You could use IsError inside of an IF statement to trap the error.
>>> =IF(ISERROR(B1-A1),0,B1-A1)
>>> where B1 is the end time and A1 is then start time. Substitute B1-A1
>>> for
>>> whatever formula you are using if it is different.
>>>
>>>
>>> "DB" wrote:
>>>
>>>> Hi...Im back again.............I thought i'd sorted it then this one
>>>> popped
>>>> up........
>>>>
>>>> I'm doing the time sheets for work (still!).........to try and keep it
>>>> simple, I input peoples start and finish times into a cell, that data
>>>> is
>>>> copied to a section lower down the page (this is eventually printed and
>>>> handed out so that the staff know who is doing what that week)
>>>> Well, it was working fine....till i tried something..
>>>>
>>>> The cells have the staff members name......their start time,their
>>>> finish
>>>> time...the finish time minus the start time gives the hours a person
>>>> works
>>>> that day.......so far so good......
>>>> If a person is off work then its displays DAY in the start time
>>>> box....and
>>>> OFF in the end time box - this makes it show #value! in the Hours
>>>> box.......this I can sort of live with....but......the worksheet also
>>>> adds
>>>> up the persons hours.......but what with this #value
>>>> in one of the cells - it is stopping it from adding up.
>>>>
>>>> If we had permanent static duties it wouldnt be a problem - but every
>>>> ow and
>>>> again a person works extra.
>>>>
>>>> How can I make an entry that will allow this ?
>>>>
>>>> All help appreciated - Dave
>>>>
>>>>
>>>>
>>>>
>>>> MONDAY DAY OFF #VALUE!
>>>> TUESDAY 08:20 17:30 9:10
>>>> WEDNESDAY 08:50 18:20 9:30
>>>> THURSDAY 08:50 12:40 3:50
>>>> FRIDAY 08:50 17:35 8:45
>>>> SATURDAY 08:50 17:35 8:45
>>>>
>>>> TOTAL HOURS #VALUE!
>>>>
>>>>
>>>>
>>>> I can email the worksheet if you want it
>>>>
>>>>
>>>>
>>
>>
>
>