How to use functions for calculating dates of other cells

**TooNisExcelled** · 05-14-2011, 09:58 PM

Ok so the formula I am trying to write, I hope it's possible.

So in cell A1. I have a received date that is entered, like a date a file was received.. Then in B1 if a date is not entered in that cell within 8 business days of the date in cell A1, then B1 will turn red and there will be a -1 (on the 9th date), -2 (on the 10th business day). So essentially B1 if no date is entered and it has exceeded 8 business days from the A1 date, there will be a negative number for how many business days it is over 8 days.

I hope that makes sense. If I need to create multiple cells to achieve this let me know.

Thanks!

**tarquinious** · 05-16-2011, 09:06 AM

Originally Posted by TooNisExcelled

Ok so the formula I am trying to write, I hope it's possible.

So in cell A1. I have a received date that is entered, like a date a file was received.. Then in B1 if a date is not entered in that cell within 8 business days of the date in cell A1, then B1 will turn red and there will be a -1 (on the 9th date), -2 (on the 10th business day). So essentially B1 if no date is entered and it has exceeded 8 business days from the A1 date, there will be a negative number for how many business days it is over 8 days.

I hope that makes sense. If I need to create multiple cells to achieve this let me know.

Thanks!

I think I've got this. In cell B1 I put in the formula:

=A1-NOW()+8

...and formatted it as a number (no decimal places)
on cell B1 I also used Conditional formatting where I selected "Use formula to determine which cells to format" and entered the formula:

=IF(INT($B$1)<=0,1,0)

...and gave it the format of a red fill

With these settings, if the date entered in A1 is before today plus 8 days (i.e. for every day that passes, the negative number will increase), it will result in a negative number, and the conditional formatting will turn it red.

**TooNisExcelled** · 05-16-2011, 10:49 PM

Originally Posted by tarquinious

I think I've got this. In cell B1 I put in the formula:

=A1-NOW()+8

...and formatted it as a number (no decimal places)
on cell B1 I also used Conditional formatting where I selected "Use formula to determine which cells to format" and entered the formula:

=IF(INT($B$1)<=0,1,0)

...and gave it the format of a red fill

With these settings, if the date entered in A1 is before today plus 8 days (i.e. for every day that passes, the negative number will increase), it will result in a negative number, and the conditional formatting will turn it red.

Thanks alot for your help. Just wondering is this achievable using google documents version of excel based spread sheets?? I am trying to make this a shared spread sheet.

Again thanks.

**arthurbr** · 05-16-2011, 09:44 AM

Originally Posted by TooNisExcelled

Then in B1 if a date is not entered in that cell within 8 business days of the date in cell A1, then B1 will turn red and there will be a -1 (on the 9th date), -2 (on the 10th business day). So essentially B1 if no date is entered and it has exceeded 8 business days from the A1 date, there will be a negative number for how many business days it is over 8 days.

So you are saying that if no date is entered in B1 within 8 working days of A1, B1 should turn red and indicate -1,-2, etc..

To fulfill these requirements you will need to use VBA. You cannot enter And a date AND a formula in the same cell

Tarquinious : NOW() gives the date AND time. OP wishes only days so you would have to use TODAY() - when calculating working days you would need the NETWORKDAYS function

This being said, I hope some VBA man can have a look at the problem

**tarquinious** · 05-16-2011, 10:30 AM

Originally Posted by arthurbr

NOW() gives the date AND time. OP wishes only days so you would have to use TODAY() - when calculating working days you would need the NETWORKDAYS function

A very good point. To get around the time issue, I used INT() to chop it out.

**NBVC** · 05-17-2011, 08:14 AM

I am not sure that the Google Docs spreadsheet allows you to conditionally format using formulas or even format based on other cells... I could be wrong...

The formulas in the sheet itself should be compatible.

**TooNisExcelled** · 05-21-2011, 09:08 PM

I tried this in Google Documents and it doesn't seem to work. It took and April date and I used 8 workdays and it took me to Dec. Seems like it took 8 and maybe used months.

Any ideas.