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

asksign

Board Regular
Joined
Mar 22, 2016
Messages
54
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

ww4612

Well-known Member
Joined
Apr 24, 2014
Messages
515
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

Board Regular
Joined
Mar 22, 2016
Messages
54
I am expecting to count worked as mentioned below. Please help me on this



Employee Name1-Mar2-Mar3-Mar4-Mar5-MarWorked Days
AAA888484.5
BBB844483.5

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

asksign

Board Regular
Joined
Mar 22, 2016
Messages
54

ADVERTISEMENT

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 Name1-Mar2-Mar3-Mar4-Mar5-Mar6-Mar7-Mar8-Mar9-Mar31-MarWorked Days
AAA48848488447.5
BBB84448888888.5

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Add up all the cells then divide by 8.
 

asksign

Board Regular
Joined
Mar 22, 2016
Messages
54

ADVERTISEMENT

Thankyou Rory...
 

asksign

Board Regular
Joined
Mar 22, 2016
Messages
54
Hi,

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
=sum(your_range)/8

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

melisen

New Member
Joined
Apr 9, 2013
Messages
13
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,494
Members
414,071
Latest member
hijackhippo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top