+ Reply to Thread
Results 1 to 7 of 7

Adding days and half days in an Attendance sheet

  1. #1
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    14

    Adding days and half days in an Attendance sheet

    Hello,

    I am keeping an attendance at work and I have to total the workers Sick days. I currently have two Total lines:

    1 Total line that sums up the accumulated FULL sick days: =COUNTIF(D9:D269,"S") and the other
    1 Total line that sums up the accumulated HALF sick days =COUNTIF(D9:D269,".5S")

    I would like to only have one Total line, therefore, is there a way and what formula do I have to use in the TOTAL cell to add up days in half days too? I tried =SUM(COUNTIFS(D9:D269,{"S",".5S"})) but this adds full days only.

    Thank you.
    Felix

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Adding days and half days in an Attendance sheet

    The simplest way is just to add your formulae together and include a factor of 2.

    To count in whole days (so getting results such as '6.5' whole days off):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    To count in half days (so '13' means 13 half-days, equivalent to 6.5 whole days):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    14

    Re: Adding days and half days in an Attendance sheet

    Hello and thank you for your reply.

    It doesn't work. I don't know how the factor 2 will help me. Maybe I don't make myself understood. I apologize. Excel is not my forte and English is not my first language.
    I need to be able to input full day (S) and half days (.5S) and add up with decimals. So if I input 4 occurrences of full sick days and 3 occurrences of half days the total line will add to 5.5 days.

    See picture with your 2*+ factor formula, it doesnt work.
    Thank you.

    ATTENDANCE.JPG

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Adding days and half days in an Attendance sheet

    There must be something I don't understand about your request, sorry, probably related to how the data is being entered or formatted.
    This should certainly be possible and I don't think it will be very complicated, but I can't work out the problem from a picture, so please attach a sample workbook instead.
    Make sure there is enough data to demonstrate your need but not so much that the file is too big (if you have hundreds of columns of data, you don't need to leave them all in, for example). Include before and after sheets in the workbook if needed to show the process you're trying to complete or automate. Make sure you include the answers you want to get - put examples in, with notes if necessary to explain details.
    Remember to remove any confidential information or, if a cell needs data in it to work, overwrite it with made-up data.
    The paperclip icon doesn't work at the moment, so click on Go Advanced under the reply box and then scroll down to Manage Attachments to attach a file.

  5. #5
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    14

    Re: Adding days and half days in an Attendance sheet

    OK, I attached the document, the Sick occurrences are under Joe Doe. He takes a day Sick and another half of day sick. I want to have the total showing 1.5 days not two entries of 1 full day and 1 half day.
    Attached Files Attached Files

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Adding days and half days in an Attendance sheet

    Please try at D293

    =SUM(COUNTIFS(D9:D269,{"S",".5S"})*{1,0.5})

    work the same as

    =COUNTIF(D9:D269,"S")+COUNTIF(D9:D269,".5S")/2

  7. #7
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    14

    Thumbs up Re: Adding days and half days in an Attendance sheet

    Yes, this is what I was looking for.
    Thank you very much and sorry again if i didn't make myself understood. I don't have any training in Excel but learning a lot from here.

    Have a great day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculating Half Days in Attendance
    By EphraimD in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 12-07-2018, 08:09 AM
  2. [SOLVED] getting back # of days including half days
    By egallag in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2018, 12:12 PM
  3. Calculating half days in attendance spreadsheet
    By TW33 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-31-2015, 03:25 PM
  4. Dates, Days and Extracting from Attendance Sheet
    By wedzmer in forum Excel General
    Replies: 15
    Last Post: 03-22-2015, 10:20 PM
  5. [SOLVED] Count 6 working days (excluding Sunday, Holidays & half days)
    By eve_star1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2015, 08:00 AM
  6. Excel Sick Days / Half Days
    By Pixie_1 in forum Excel General
    Replies: 6
    Last Post: 02-28-2007, 08:28 AM
  7. [SOLVED] How do I track half days in the attendance tracking template?
    By Business Manager in forum Excel General
    Replies: 1
    Last Post: 10-20-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1