Working on a timesheet I want to calculate + and - time value over the period
of one month. I have tried the Countif but cannot get this to work properly.
Can anyone help please?

On Jan 28, 11:50 am, PCERM wrote:
Working on a timesheet I want to calculate + and - time value over the period
of one month. I have tried the Countif but cannot get this to work properly.
Can anyone help please?

Hi,

Can you elaborate on this? What does the data in your timesheet look
like?

Kind regards,
Matt Richardson
http://teachr.blogspot.com

Hi Matt

Yes, This is a felxible working sheet and is broken down into days of the
month and each day is calculated at 7 hours worktime with one hour for lunch.
People enter their own time that they come to work and leave and how much
time they take for lunch. We therefore have 2 columns, one for lunch
calculating how much time is taken at lunch and at the end of the day whether
someone has worked more or less than 7 hours. I want to try and calculate
over a period of one month on these two columns specifically how much time
someone has worked over 7 hours and how much they are under 7 hours. I also
want to calculate if someone is taken less then the one hour lunch as this
contributes to their overall time. All numbers are calulcated in time ie
hh:mm:ss and show both negative and positive times in terms of conditional
formatting denoting colour differences.

Hope this helps and thanks for taking the time to respond.

Peter


"Matt Richardson" wrote:

On Jan 28, 11:50 am, PCERM wrote:
Working on a timesheet I want to calculate + and - time value over the period
of one month. I have tried the Countif but cannot get this to work properly.
Can anyone help please?

Hi,

Can you elaborate on this? What does the data in your timesheet look
like?

Kind regards,
Matt Richardson
http://teachr.blogspot.com

As you want to possibly show negative time I would switch the spreadsheet to
the 1904 date system: Tools Options Calculation 1904 Date System.

Assumoing that your sheet is something like:

Column A Name
Column B Date
Column C Start
Column D Lunch Out
Column E Lunch In
Column F Finish
Column G Hours Worked
Column H Balance of hours with 7:00 in H1

Formula in G2:
=IF(COUNT(C2:F2)=4,D2-C2+F2-E2,"")

Formula in H2:
=IF(G2<"",G2-$H$1,"")






--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"PCERM" wrote in message
...
Hi Matt

Yes, This is a felxible working sheet and is broken down into days of the
month and each day is calculated at 7 hours worktime with one hour for
lunch.
People enter their own time that they come to work and leave and how much
time they take for lunch. We therefore have 2 columns, one for lunch
calculating how much time is taken at lunch and at the end of the day
whether
someone has worked more or less than 7 hours. I want to try and calculate
over a period of one month on these two columns specifically how much time
someone has worked over 7 hours and how much they are under 7 hours. I
also
want to calculate if someone is taken less then the one hour lunch as this
contributes to their overall time. All numbers are calulcated in time ie
hh:mm:ss and show both negative and positive times in terms of conditional
formatting denoting colour differences.

Hope this helps and thanks for taking the time to respond.

Peter


"Matt Richardson" wrote:

On Jan 28, 11:50 am, PCERM wrote:
Working on a timesheet I want to calculate + and - time value over the
period
of one month. I have tried the Countif but cannot get this to work
properly.
Can anyone help please?

Hi,

Can you elaborate on this? What does the data in your timesheet look
like?

Kind regards,
Matt Richardson
http://teachr.blogspot.com

Hi Sandy

Thanks for your response and yes I am already using the 1904 system to work
on the negative times. My columns a

A - Date
B - Work Start Time
C - Lunch Start Time
D - Lunch taken (C - E)
E - Lunch Stop Time
F - Work Stop Time
G - Total worked Time (F - B - D)
H - Time +/- against 7 hours (G-"07:00")

I tried converting your calculation formula in G2 below but that shows
nothing at all unless I am doing it wrong again. I alkso want to work out
both the negative balance across a month and the positive along with a
difference of the 2. ie if I work 7 hrs and 5 mins today then 6 hrs and 30
mins tomorrow, calculation would show +5 mins and -30 mins as two
calculations with the balance being -25 mins. Hope that makes sense.

Thanks again for your help on this.

Peter

"Sandy Mann" wrote:

As you want to possibly show negative time I would switch the spreadsheet to
the 1904 date system: Tools Options Calculation 1904 Date System.

Assumoing that your sheet is something like:

Column A Name
Column B Date
Column C Start
Column D Lunch Out
Column E Lunch In
Column F Finish
Column G Hours Worked
Column H Balance of hours with 7:00 in H1

Formula in G2:
=IF(COUNT(C2:F2)=4,D2-C2+F2-E2,"")

Formula in H2:
=IF(G2<"",G2-$H$1,"")






--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"PCERM" wrote in message
...
Hi Matt

Yes, This is a felxible working sheet and is broken down into days of the
month and each day is calculated at 7 hours worktime with one hour for
lunch.
People enter their own time that they come to work and leave and how much
time they take for lunch. We therefore have 2 columns, one for lunch
calculating how much time is taken at lunch and at the end of the day
whether
someone has worked more or less than 7 hours. I want to try and calculate
over a period of one month on these two columns specifically how much time
someone has worked over 7 hours and how much they are under 7 hours. I
also
want to calculate if someone is taken less then the one hour lunch as this
contributes to their overall time. All numbers are calulcated in time ie
hh:mm:ss and show both negative and positive times in terms of conditional
formatting denoting colour differences.

Hope this helps and thanks for taking the time to respond.

Peter


"Matt Richardson" wrote:

On Jan 28, 11:50 am, PCERM wrote:
Working on a timesheet I want to calculate + and - time value over the
period
of one month. I have tried the Countif but cannot get this to work
properly.
Can anyone help please?

Hi,

Can you elaborate on this? What does the data in your timesheet look
like?

Kind regards,
Matt Richardson
http://teachr.blogspot.com

Peter,

Doing what you are suggesting works "as advertised" for me. Send me a copy
of your spreadsheet by replacing the part of my address after the @ sig as
it says in my signature and I will take a look.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"PCERM" wrote in message
...
Hi Sandy

Thanks for your response and yes I am already using the 1904 system to
work
on the negative times. My columns a

A - Date
B - Work Start Time
C - Lunch Start Time
D - Lunch taken (C - E)
E - Lunch Stop Time
F - Work Stop Time
G - Total worked Time (F - B - D)
H - Time +/- against 7 hours (G-"07:00")

I tried converting your calculation formula in G2 below but that shows
nothing at all unless I am doing it wrong again. I alkso want to work out
both the negative balance across a month and the positive along with a
difference of the 2. ie if I work 7 hrs and 5 mins today then 6 hrs and
30
mins tomorrow, calculation would show +5 mins and -30 mins as two
calculations with the balance being -25 mins. Hope that makes sense.

Thanks again for your help on this.

Peter

"Sandy Mann" wrote:

As you want to possibly show negative time I would switch the spreadsheet
to
the 1904 date system: Tools Options Calculation 1904 Date System.

Assumoing that your sheet is something like:

Column A Name
Column B Date
Column C Start
Column D Lunch Out
Column E Lunch In
Column F Finish
Column G Hours Worked
Column H Balance of hours with 7:00 in H1

Formula in G2:
=IF(COUNT(C2:F2)=4,D2-C2+F2-E2,"")

Formula in H2:
=IF(G2<"",G2-$H$1,"")






--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"PCERM" wrote in message
...
Hi Matt

Yes, This is a felxible working sheet and is broken down into days of
the
month and each day is calculated at 7 hours worktime with one hour for
lunch.
People enter their own time that they come to work and leave and how
much
time they take for lunch. We therefore have 2 columns, one for lunch
calculating how much time is taken at lunch and at the end of the day
whether
someone has worked more or less than 7 hours. I want to try and
calculate
over a period of one month on these two columns specifically how much
time
someone has worked over 7 hours and how much they are under 7 hours. I
also
want to calculate if someone is taken less then the one hour lunch as
this
contributes to their overall time. All numbers are calulcated in time
ie
hh:mm:ss and show both negative and positive times in terms of
conditional
formatting denoting colour differences.

Hope this helps and thanks for taking the time to respond.

Peter


"Matt Richardson" wrote:

On Jan 28, 11:50 am, PCERM wrote:
Working on a timesheet I want to calculate + and - time value over
the
period
of one month. I have tried the Countif but cannot get this to work
properly.
Can anyone help please?

Hi,

Can you elaborate on this? What does the data in your timesheet look
like?

Kind regards,
Matt Richardson
http://teachr.blogspot.com