Derive the the number of days based on the given date for the week

[Excel God]

Hi forum members,

I need a formula to help me perform a function that returns the number of days based on the date for the week.
For example,
Cell A1: 22/6/18 (DATE)
Cell A2: 3 (FORUMULA)

So basically, i need a formula in cell a2 to calculate the the remaining number of days left for the week, including the day itself
so the above example date is on a friday, thus cell a2 will have a value of 3 since there are 3 days left till sunday, which is the end of the week.

Please help a me out!

[AliGW]

This will do what you want:

=7-WEEKDAY(A1,11)+1

However, when the date in question is a Sunday, it will return 1, so you could modify it thus:

=IF(WEEKDAY(A1,11)=7,0,7-WEEKDAY(A1,11)+1)

[AliGW]

Thanks for the rep!