-1

I have a schedule of a team planned on multiple projects on certain dates.

I'm looking for a formula that retrieves the amount of days (cells) on PROJECT-A until TODAY().

enter image description here

4
  • Hi and welcome to WebApps. The title of your question is not the same as the description in the body of question or your sample data. Would you please describe EXACTLY what you are trying to calculate; using your sample data, please provide an answer that you would calculate and the logic that you use to calculate that answer; and describe where, in the spreadsheet, the answer should appear.
    – Tedinoz
    Mar 19 at 3:41
  • Hi Tedinoz, Thanks for your reply and sorry my question was not clear. Going to try again ;) I want retrieve the total amount of days worked on Project A for each name but between the time period of beginning of the year until today. The answer will be in the spreadsheet in antother tab. Mar 20 at 12:08
  • Thanks. Does the data map actual AND planned time worked, or just actual time worked? (Trying to understand if you want a day-by-day total, or just a single total). Also, because of AM and PM shifts, an “even” count = full days but an “odd” count will include a half a day. Correct? AAMOI, have you tried, “COUNTIF”?
    – Tedinoz
    Mar 20 at 22:31
  • I want to have the actual time worked and a the end of the formula I would divide everything by 2 to tackle the AM and PM. I did try the COUNTIFS but I'm stuck when I involve the dates in it. I was trying the following formula: =COUNTIFS(F:G,"PROJECT-A;", C:C,"<="&today()) --> to calculate all worked days on project a for NAme B until today. Mar 21 at 8:34

1 Answer 1

0
  • Cell C4: Enter =today()
  • Cell G1: Enter the project name
  • Cell E4: Enter this formula

=if(mod(((countifs($C5:$C42,"<="&$C$4,D5:D42,"="&$G$1))+(countifs($C5:$C42,"<="&$C$4,E5:E42,"="&$G$1))),2)=0,((countifs($C5:$C42,"<="&$C$4,D5:D42,"="&$G$1))+(countifs($C5:$C42,"<="&$C$4,E5:E42,"="&$G$1)))/2,(((countifs($C5:$C42,"<="&$C$4,D5:D42,"="&$G$1))+(countifs($C5:$C42,"<="&$C$4,E5:E42,"="&$G$1)))/2))

  • Copy/paste the formula into cells:
    • G4, I4, K4

screenshot


LOGIC

AM and PM

COUNTIFS() evaluates for values on the same row. This means that a single formula evaluating the date, AM Column and PM column can't be used since it would detect only full days, and ignore half days.

So there must be two formulas: one to the AM column, and another for the PM column.

In addition, the total AM plus PM value needs to halved; I used MOD to detect the half day but there are other methods.

Absolute vs Relative values

  • The reference to the Date Column (Column C) is absolute
  • the references to the AM and PM column are relative
  • this allows the formula to be copied across the sheet (every second column) and the result will accurately reflect the respective columns for that Name.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.