How to calculate 8 as 1 and 4 as 0.5 in excel

[asksign]

Hi,

I am dealing with 400 employees in an organization, I need help to maintain attendance report. We are working 8 hours a day and 4 hours as half-a-day. I have to calculate 8 as 1 and 4 as 0.5 for the day for an employee. Please help me on this.

 

[Macropod]

You could just divide both numbers by 8.

 

[ww4612]

does an employee work 07:59:59.999 a day count as work for half day?
does an employee work 3:59:59.999 a day count as absent for the day?

 

[asksign]

I am expecting to count worked as mentioned below. Please help me on this

Employee Name	1-Mar	2-Mar	3-Mar	4-Mar	5-Mar	Worked Days
AAA	8	8	8	4	8	4.5
BBB	8	4	4	4	8	3.5

<colgroup><col><col span="5"><col></colgroup><tbody>
</tbody>

 

[asksign]

Hi Paul,

You are correct when one cell has to be done but in my case i have to count for a whole month, Please find the requirement mentioned below for your reference.

Employee Name	1-Mar	2-Mar	3-Mar	4-Mar	5-Mar	6-Mar	7-Mar	8-Mar	9-Mar	31-Mar	Worked Days
AAA	4	8	8	4	8	4	8	8	4	4	7.5
BBB	8	4	4	4	8	8	8	8	8	8	8.5

<colgroup><col><col span="4"><col span="5"><col><col></colgroup><tbody>
</tbody>

 

[RoryA]

Add up all the cells then divide by 8.

 

[asksign]

Thankyou Rory...

 

[asksign]

Hi,

Is there any specific formula to count 8 as 1 and 4 as 0.5, Please help me in this.

 

[RoryA]

=sum(your_range)/8

or you could use:
=countif(your_range,8)+countif(your_range, 4)/2

 

[melisen]

No. There isn´t any =eightasoneandfourashalf() functions in Excel. You could make one in VBA...

But just divide by eight as suggested. That would be =sum(B2:AF2)/8 if your month (31 cells) is in the B2 to AF2 range.

To expand a bit, I assume only 4´s or 8´s are valid inputs. To make sure only those numbers are in the range you could set up a data validation on the cells. If other numbers are used and you want to conform them into a certain schema them things get a bit more complicated.