+ Reply to Thread
Results 1 to 4 of 4

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
    4

    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 NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,907

    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

    My internet connection can be intermittent, so sometimes I may not reply quickly to questions. Sorry - it's not personal and 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
    4

    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 NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,907

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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