In Australia our financial year is from 1 July to 30 June. If you need to work with days elapsed or days remaining in the financial year you can use a few formulas to do the calculations for you.

Calendar Days

When working with days you can use the DATE function to calculate to correct financial year start and end, based on the date itself. The image below shows the results. A file with the examples can be downloaded from a link at the bottom of the post.

The formula in cell B2 is

=DATE(YEAR(A2)+(MONTH(A2)>6)*1,6,30)-A2

The DATE function syntax has three arguments.

DATE(Year, Month, Day)

The three arguments all need to be values, or text that can be converted to values.

To calculate the end of the financial year for a date we can hard key the Month (6) and Day (30) as we know what they are. We don’t know what the year should be, but we can calculate it based on the date.

Calculating the year

I have used a logical calculation to determine the year to use. You could have used an IF function but logical calculations are often shorter and quicker. The section of the formula that calculates the year is:

YEAR(A2)+(MONTH(A2)>6)*1

We take the year from the date in A2 and then need to figure out if we need to add one to it for the financial year end. Month numbers that are greater than 6 (July to December) need to have the one added. The part of the formula that determines whether to add one is:

(MONTH(A2)>6)*1

The logical test between the parentheses is usually seen in an IF function, but you can use it separately to return a TRUE or FALSE. When the month number is above 6 it will return TRUE, otherwise it will return FALSE.
In Excel TRUE = 1 and FALSE = 0. By multiplying by 1 you convert TRUE into 1 and FALSE into 0. When you multiple by TRUE, or 1, you don’t change the value. When you multiply by FALSE, or zero, you zero the number.

Hence, when the month number is above 6 it will add 1 to the year, otherwise the year is left unchanged.

So the DATE function is used to calculate the end of the financial year and then subtract the date in A2 from it to calculate how many days remain in the financial year.

Elapsed Days

We can use a similar formula to calculate how many days have elapsed in the financial year. The formula in C2 is:

=A2-DATE(YEAR(A2)+(MONTH(A2)<7)*-1,7,1)+1

This needs to decide whether to deduct a year from the current date to determine the starting year number for first day of the financial year.

Note: one is added because it finds the difference in days and not elapsed days. Elapsed days need to include the current day as well. Eg if you calculated the number of days from 4 July 2014 it would return 3 (4 minus 1) but in effect there are 4 days elapsed, hence one is added.

Weekdays and Workdays

You can use the above beginning and end dates to calculate how many workdays and weekdays remaining and elapsed. The function used in the NETWORKDAYS function. Its syntax is

NETWORKDAYS(Start_Date,End_Date,Holidays_Range)

The Holidays _Range is optional and is a list of dates to exclude as workdays. If excluded, the result is weekdays between two dates. The result is inclusive – both the start and end dates are included in the result.

Weekdays

The image below shows weekday calculations.

The formula for cell B2 is:

=NETWORKDAYS(A2,DATE(YEAR(A2)+((MONTH(A2)>6)*1),6,30))-1

One is deducted because it calculates inclusive results and we want exclusive – the date is not to be included as one of the days.

The formula for C2 is:

=NETWORKDAYS(DATE(YEAR(A2)+((MONTH(A2)<7)*-1),7,1),A2)

For elapsed we want an inclusive calculation.

Workdays

The workday formulas are the same as the weekday calculations above, but with an added range holding the public holidays. As per the image below.

The formula in B2 is:

=NETWORKDAYS(A2,DATE(YEAR(A2)+((MONTH(A2)>6)*1),6,30),$F$2:$F$11)-1

The formula in C2 is:

=NETWORKDAYS(DATE(YEAR(A2)+((MONTH(A2)<7)*-1),7,1),A2,$F$2:$F$11)

The file with the example can be downloaded below

Financial_Year_examples