ExcelBanter - sum of timesheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - sum of timesheet (https://www.excelbanter.com/excel-discussion-misc-queries/264658-sum-timesheet.html)

nidabland

sum of timesheet

Let's say A1: 9 am
A2: 12 pm
A3: 12:30 pm
A4: 3 pm
I need the total in A5 to calculate how many hours worked, but also if I
write "off" in A1:A4, I need A5 to say "off", so basically I need either the
total hours worked or the word "off" in A5. Is this possible? Thanks for
any help,

Bob Phillips[_4_]

sum of timesheet

Try

=IF(COUNTIF(A1:A4,"off")=4,"off",SUM(A1:A4))

--

HTH

Bob

"nidabland" wrote in message
...
Let's say A1: 9 am
A2: 12 pm
A3: 12:30 pm
A4: 3 pm
I need the total in A5 to calculate how many hours worked, but also if I
write "off" in A1:A4, I need A5 to say "off", so basically I need either
the
total hours worked or the word "off" in A5. Is this possible? Thanks for
any help,

Fred Smith[_4_]

sum of timesheet

Is "off" in *every* cell of a1:a4, or *any* cell?
If every, try:
=if(countif(a1:a4,"off")=4,"off",a2-a1+a4-a3)

If any, change =4 to =1

Regards,
Fred

"nidabland" wrote in message
...
Let's say A1: 9 am
A2: 12 pm
A3: 12:30 pm
A4: 3 pm
I need the total in A5 to calculate how many hours worked, but also if I
write "off" in A1:A4, I need A5 to say "off", so basically I need either
the
total hours worked or the word "off" in A5. Is this possible? Thanks for
any help,

All times are GMT +1. The time now is 02:42 PM.