Excel-formula required to calculate number of days remaining

Hi,
A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.


A1 B1 C1 D4
2 4 6 C1-Formula is: =sum(A1+B1)

Now I have to write formula for D4-cell:
i.e. =sum(C1/Total days remaining of the month)

Suppose Today is April 01, 2009, C1/should be devided by 30,
Tomorrow will be April 02, 2009, C1/should be devided by 29.

Please advise about formula for cell-D4. Thanks.

Hi,
A1, B1, C1 and D1 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively.

A1 value is 2
B1 value is 4
C1 values is 6 --C1-Formula is: =sum(A1+B1)

Now I have written formula for D1-cell:
=SUM(C1/(32-DAY(TODAY()+31)-4))
Note: I substracted -4 in above formula because there are four sundays are in April, 2009 and I want to keep days only working days (Total days in April are 30 - sundays which are 4 = 26.)

This spreadsheet will divide c1/26 on Wed April 1, 2009 according to formula.
This spreadsheet will divide c1/25 on Thu April 2, 2009 according to formula.
This spreadsheet will divide c1/24 on Fri April 3, 2009 according to formula.
This spreadsheet will divide c1/23 on Sat April 4, 2009 according to formula.
This spreadsheet will divide c1/22 on Sun April 5, 2009 according to formula.
This spreadsheet will divide c1/21 on Mon April 6, 2009 according to formula.

The problem is in Sunday calculation because Sunday is a Holiday and on Sunday the formula should be same as Sat C/23. On Monday formula should be C1/22 because working day has been started. How can I avoid calculation on date on Sundays and ignore decrease number on sundays. At the end of month on April 30, 2009 the formula should be C1/1.

Please help in this situation/formula and advise about formula for cell-D4. Thanks.

Hi,

Could I make a suggestion. By using the weekday function would that assist you.

ie =weekday(today()) would return a weekday value. Sunday = 1, maybe a >1 IF statement may help.

Try it.

Cheers


Tony

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Hi,

I have been having some fun with this one, as per attachment.

I cannot see this being done in a one cell formula, to allow for Sundays off.

I don't understand the significance of Cells A1 to D1.

Anyway, here it is.

Cheers, Tony

TonyB51,

here is 1 cell version!!

Thread closed - cross posting