Excel formula which counts the day within the quarter

Hi

I have been really struggling to get my head around an formula and I am sure someone on here will be able to help..

What I would like to do is for a date column (format: DD/MM/YYYY) return the number day it is within the quarter. This will obviously reset each quarter.

I would like to do this for workdays if possible, but either or would be fine..

ALL DAYS

--For example: if the date in the dates column said (01/01/2019) for Q1 would return 1. And (01/02/2019) would return 32... up until the next quarter

First quarter, Q1: 1 January – 31 March (90 days or 91 days in leap years)
Second quarter, Q2: 1 April – 30 June (91 days)
Third quarter, Q3: 1 July – 30 September (92 days)
Fourth quarter, Q4: 1 October – 31 December (92 days)

WORKDAYS ONLY (Monday-Friday)

--- For example: if the date in the dates column said (01/01/2019) for Q1 would return 1. And (07/01/2019) would return 5.. up until the next quarter


--------------------------------------------------

The whole point of this exercise is to populate a graph with cumulative values showing the current total in the quarter.
So:
X AXIS - Day/or workday within a quarter
Y AXIS - Cumulative sum

---------------------------

NB: (for extracting the day I usually use the =DAY function)

Any suggestions?
Help would be much appreciated

Hi

Suppose your data in A2:A...

Try this formula in B2 and drag down

Formula:

Please Login or Register  to view this content.

(for all days)

Suppose Holidays in F2:F20

For working days you can use this formula

Formula:

Please Login or Register  to view this content.

Amazing thankyou very much Jose, fixed!

You're welcome. I liked having helped.

Do not forget to mark this thread as solved.

Best regards.