Forum Discussion

Karen Gaston's avatar
Karen Gaston
Copper Contributor
Sep 17, 2018

Calculate # of days from start & finish dates etc.

Hi all. I'm needing to calculate the number of days employed for the financial year if there is either a start and/or a finish date. But if both are blank, as it is for the employee in Row 7, then I need it to return the value of 365. For the remainder of the formula I need it to calculate days worked in the year if either start or finish dates are entered (column J or K) or both have an entry. That's why I have the start of Financial Year and End of Financial Year dates in cells L3 & L4 to use as a calculation base. But just not sure how to include all those variables in the formula.
Hoping you can help. See below screenshot. Thanks in advance.

 

 

excel
Formulas and Functions

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 17, 2018

    Hi Karen,

     

    Perhaps if FY starts on Jul 01, it ends on Jun 30, not on the day when next FY starts. And you have date out of FY, is that typo or not?

    For such data formula could be

    =MIN($L$4*(K6<$L$3)+K6-MAX(J6,$L$3)+1,365)

    if end date is out of FY, otherwise MIN() is not required

Resources