Time Sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am making my own, crude, "punch card"
Column b= Time In
Column c= Lunch Out
Column d= Lunch In
Column e= Time out.
Column f= Total time worked for the day shown in hours and minutes.

I have used this function to calculate the total time worked for the day:

=TEXT((E4-B4)-(D4-C4),"h:mm") (If there is a more efficient way please let me know)

First issue is that I don't want this to calculate if any of the columns are empty. Not sure how to do this. Can you help?
Second issue is:
I want to total the time worked for the end of the week. I have tried SUM, and writing the formula using cell1+cell2+cell3 etc. but I can not get the formula to work. This is the last one I tried:
=TEXT(SUM(F4:F8),"h:mm") This returns 00:00. What am I doing wrong? I want it to keep a running total of the hours I work in a week without being thrown off by the days that aren't yet worked.

Thanks for the help!

Matt
 
First of all, why do you use text? Instead use this in F4

=IF(COUNT(B4:E4)=4,E4-B4-(D4-C4),"")

copy down


for the total of the week use

=SUM(F4:F8)

now here comes an important thing, format the total cell as [h]:mm
that way it will roll over 24 hours

--

Regards,

Peo Sjoblom

Matt said:
I am making my own, crude, "punch card"
Column b= Time In
Column c= Lunch Out
Column d= Lunch In
Column e= Time out.
Column f= Total time worked for the day shown in hours and minutes.

I have used this function to calculate the total time worked for the day:

=TEXT((E4-B4)-(D4-C4),"h:mm") (If there is a more efficient way please let me know)

First issue is that I don't want this to calculate if any of the columns
Click to expand...
are empty. Not sure how to do this. Can you help?
Second issue is:
I want to total the time worked for the end of the week. I have tried
Click to expand...
SUM, and writing the formula using cell1+cell2+cell3 etc. but I can not get
the formula to work. This is the last one I tried:
=TEXT(SUM(F4:F8),"h:mm") This returns 00:00. What am I doing wrong? I
Click to expand...
want it to keep a running total of the hours I work in a week without being
thrown off by the days that aren't yet worked.
 
Thanks for the tip! I can't wait to try it. I used TEXT because that was what was shown somewhere in the Office help files on line here. I wasn't initially using it and I was getting a bum result. Adding TEXT seemed to fix it. Aside from the fact that COUNT will probably solve my problem, what is the advantage to using it vs. TEXT

Thanks again for the help

Matt
 
OK I took the time to understand the formula you suggested. I think I might still need to put TEXT into the equation. What is the proper syntax?

If(COUNT(B4:E4)=4,TEXT((E4-B4)-(D4-C4),"h:mm")

Is that nested properly?

I used TEXT because B4-E4 are formatted as H:MM:SS If I remember correctly the TEXT function converts the date to a numeric value therefore making the subtraction that I need possible. Does that sound right?

Thanks again,

matt

----- Peo Sjoblom wrote: -----

First of all, why do you use text? Instead use this in F4

=IF(COUNT(B4:E4)=4,E4-B4-(D4-C4),"")

copy down


for the total of the week use

=SUM(F4:F8)

now here comes an important thing, format the total cell as [h]:mm
that way it will roll over 24 hours

--

Regards,

Peo Sjoblom

Matt said:
I am making my own, crude, "punch card"
Column b= Time In
Column c= Lunch Out
Column d= Lunch In
Column e= Time out.
Column f= Total time worked for the day shown in hours and minutes.
Click to expand...
are empty. Not sure how to do this. Can you help?
Second issue is:
I want to total the time worked for the end of the week. I have tried
Click to expand...
SUM, and writing the formula using cell1+cell2+cell3 etc. but I can not get
the formula to work. This is the last one I tried:
=TEXT(SUM(F4:F8),"h:mm") This returns 00:00. What am I doing wrong? I
Click to expand...
want it to keep a running total of the hours I work in a week without being
thrown off by the days that aren't yet worked.
 
The reason I used count is that you said
"First issue is that I don't want this to calculate if any of the columns
are empty."
and since time values are numbers (one hour = 1/24) so if count does not
return 4, it will return a blank cell.
That will solve that condition.

E4-B4-(D4-C4)

will return hours worked, assuming you don't work after midnight, if you do
that use

=IF(COUNT(B4:E4)=4,MOD(E4-B4-(D4-C4),1),"")

and the important thing with the custom format (do
format>cells>numbers>custom)
[h]:mm will make it possible to return a value greater than 24:00.
If you need to multiply this with an hourly rate and the rate is for example
in H4

=ROUND(SUM(F4:F8)*24*H4,2)

format as currency 2 decimals

--

Regards,

Peo Sjoblom

Matt said:
Thanks for the tip! I can't wait to try it. I used TEXT because that was
Click to expand...
what was shown somewhere in the Office help files on line here. I wasn't
initially using it and I was getting a bum result. Adding TEXT seemed to
fix it. Aside from the fact that COUNT will probably solve my problem, what
is the advantage to using it vs. TEXT?
 
No, the text function makes it text, it doesn't really matter since if you
do calculations with text numbers that will
turn to numeric but it adds an unnecessary extra function

--

Regards,

Peo Sjoblom

Matt said:
OK I took the time to understand the formula you suggested. I think I
Click to expand...
might still need to put TEXT into the equation. What is the proper syntax?
If(COUNT(B4:E4)=4,TEXT((E4-B4)-(D4-C4),"h:mm")

Is that nested properly?

I used TEXT because B4-E4 are formatted as H:MM:SS If I remember
Click to expand...
correctly the TEXT function converts the date to a numeric value therefore
making the subtraction that I need possible. Does that sound right?
Thanks again,

matt

----- Peo Sjoblom wrote: -----

First of all, why do you use text? Instead use this in F4

=IF(COUNT(B4:E4)=4,E4-B4-(D4-C4),"")

copy down


for the total of the week use

=SUM(F4:F8)

now here comes an important thing, format the total cell as [h]:mm
that way it will roll over 24 hours

--

Regards,

Peo Sjoblom

Matt said:
I am making my own, crude, "punch card"
Column b= Time In
Column c= Lunch Out
Column d= Lunch In
Column e= Time out.
Column f= Total time worked for the day shown in hours and minutes.
Click to expand...
please
let me know) columns
are empty. Not sure how to do this. Can you help?
Second issue is:
I want to total the time worked for the end of the week. I have
Click to expand...
tried
SUM, and writing the formula using cell1+cell2+cell3 etc. but I can not get
the formula to work. This is the last one I tried:
=TEXT(SUM(F4:F8),"h:mm") This returns 00:00. What am I doing
Click to expand...
wrong? I
want it to keep a running total of the hours I work in a week without being
thrown off by the days that aren't yet worked.
Click to expand...
 
Hi

Another way
=(COUNT(B4:E4)=4)*(E4-B4-D4+C4)
(It'l return 0 until all values are filled in)

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


Peo Sjoblom said:
First of all, why do you use text? Instead use this in F4

=IF(COUNT(B4:E4)=4,E4-B4-(D4-C4),"")

copy down


for the total of the week use

=SUM(F4:F8)

now here comes an important thing, format the total cell as [h]:mm
that way it will roll over 24 hours

--

Regards,

Peo Sjoblom

Matt said:
I am making my own, crude, "punch card"
Column b= Time In
Column c= Lunch Out
Column d= Lunch In
Column e= Time out.
Column f= Total time worked for the day shown in hours and minutes.

I have used this function to calculate the total time worked for the day:

=TEXT((E4-B4)-(D4-C4),"h:mm") (If there is a more efficient way please let me know)

First issue is that I don't want this to calculate if any of the columns
Click to expand...
are empty. Not sure how to do this. Can you help?
Second issue is:
I want to total the time worked for the end of the week. I have tried
Click to expand...
SUM, and writing the formula using cell1+cell2+cell3 etc. but I can not get
the formula to work. This is the last one I tried:
=TEXT(SUM(F4:F8),"h:mm") This returns 00:00. What am I doing wrong? I
Click to expand...
want it to keep a running total of the hours I work in a week without being
thrown off by the days that aren't yet worked.
Thanks for the help!

Matt
Click to expand...
Click to expand...
 
Ok, I used your formula and it works. But I have 2 problems yet to solve. The first is: =sum(f3:f8) does not tally the total hours worked for the week, accurately. I used the "13:30" format before I switched to an HH:MM:SS format. This properly reflects a number that is close to the hours worked (I worked 43+ hours this calculated 44:36) but it is not accurate. What can I do?

Matt
 
When you say not accurate what do you mean? What do you expect and what do
you get?
I have done a lot of time sheets and I never had any problems with
inaccuracy.
Do you use [h]:mm as format for the totals?

--

Regards,

Peo Sjoblom

Matt said:
Ok, I used your formula and it works. But I have 2 problems yet to solve.
Click to expand...
The first is: =sum(f3:f8) does not tally the total hours worked for the
week, accurately. I used the "13:30" format before I switched to an
HH:MM:SS format. This properly reflects a number that is close to the hours
worked (I worked 43+ hours this calculated 44:36) but it is not accurate.
What can I do?
 
Back
Top