Basic excel formula to count days?

[BuRaK]

Is there way to format a cell to display two sets of numbers based on the date in two sets of fields?

ie.
field1: 6/1/2013
field2: 7/1/2013
field3: 36/66 (formula goes here)
displays number of days between today to the day in field1/field2 remaining

 

[compnet]

if they are in date format (press ctrl+1 on the cell and select date), then

if a1 = 6/1/13
and a2 = 7/1/13

=a2-a1 will give you the difference in days, or 30


edit: =today() give you current date

edit edit: so

a1) 6/1/13
a2) 7/1/13
a3) =CONCATENATE(A1-TODAY(),"/",B1-TODAY())

a3 will return 68/38

 

[BuRaK]

Thank you! My mistake here with poor explanation.

Field = column

So I need to track how many days are left until the date in a given column arrives. So if there is 60 days left it would print 60

I mentioned field1 and 2 thinking I can use a single cell to print how many days left to 2 different columns but it does not have work that way

THANK YOU!

 

[BuRaK]

PERFECT, THANK YOU!!!!!!!!

One minor detail. If the a2 is blank or letters in it (such as NA) formula goes nuts. Is there a way to skip calculation if the field says NA?

YOU'RE A GENIUS!

if they are in date format (press ctrl+1 on the cell and select date), then

if a1 = 6/1/13
and a2 = 7/1/13

=a2-a1 will give you the difference in days, or 30


edit: =today() give you current date

edit edit: so

a1) 6/1/13
a2) 7/1/13
a3) =CONCATENATE(A1-TODAY(),"/",B1-TODAY())

a3 will return 68/38

 

[BuRaK]

One more small detail. Is there a way if the result is 30 or less, it displays in red color?

 

[compnet]

Google conditional formatting excel.. I'm at lunch I'll explain if you can't figure it out

 

[BuRaK]

FOund conditional formatting but does not seem to work since the formula result is 2 numbers separated by /

 

[compnet]

Ah, ya, probably the easiest would be to seperate it back in to two columns then.

 

[compnet]

For blank a2

=if(count(a2)> 0,"formula from above", 0)

For conditional formattin, instead of cell value, you should be able to do another formula in one of the if fields - =a2-today <30 format red.. when I get back ill check

 

[BuRaK]

Could not get it work

It says parse error

 

[compnet]

Could not get it work

It says parse error

which one?

for the conditional formatting, click cond formatting, new rule, use a formula, and then use this formula -
=$A$1-TODAY()<30

for the blank field -
=IF(COUNT(A1)> 0,CONCATENATE(A1-TODAY(),"/",B1-TODAY()),0)

 

[BuRaK]

Conditional formatting does not work in Google Docs. Requires a script. I'm just trying to get the formula to not show error if column a1 or a2 has no dates entered so it would be like

empty a2 = 30/
empty a1 = /30
or both full = 30/30

 

[BuRaK]

This formula does the following:
=IF(COUNT(A1)> 0,CONCATENATE(A1-TODAY(),"/",B1-TODAY()),0)


a1 empty = a3 becomes 0
a1 full a2 empty = a3 shows some crazy result

 

[compnet]

=CONCATENATE(IF(COUNT(A1)>0,A1-TODAY(),0),"/",IF(COUNT(A2)>0,A2-TODAY(),0))

not sure about conditional formatting in google docs

 

[BuRaK]

Works like a charm!!!!

Thank you

Is there anyway to make it not display a number instead of "0" when there is no date?

 

[compnet]

works like a charm!!!!

Thank you

is there anyway to make it not display a number instead of "0" when there is no date?

=concatenate(if(count(a1)>0,a1-today()," "),"/",if(count(a2)>0,a2-today()," "))