Dates

[smooney]

I'm wondering if there is a formula that will calculate the number of days
from a given date until the end of the year. So if I have a date of Mar 31,
2007...I want to know how many days (including that day) between then and Dec
31.

Thanks

 

[Marcelo]

Hi,
try to use the DATEDIF funcion, as:

=datedif(a2;a1;"d")

assuming a1 = 12/31/2007 and a2 = 03/31/2007

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"smooney" escreveu:

 

[Marcelo]

sorry for the mistake use
=datedif(a2,a1,"d")

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

 

[smooney]

Thank you....but unfortunately it's not working for me. All I get in the
cell is: #VALUE!

 

[TRYING]

Since Excel dates are really integers, just type 12/31 (Excel will
automatically supply the current year) in cell A1, type 3/31 in cell A2, and
the formula =A1-A2+1 in cell A3. You should get 276 as the answer.

 

[Marcelo]

datedif(beg,end,"D")

beg = begning date
end = end date,

try it

=datedif(date(2007,03,31),date(2007,12,31),"D")

hth

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"smooney" escreveu:

 

[smooney]

Thank you but it looks like I need to format the cells? When I enter 12/31
and 03/31 it defaults to Dec-31 and Mar-31 and all I get back is Oct-00.

 

[TRYING]

Yes, format cell A3, if that is where your formula is, as General in the
Number tab.

 

[TRYING]

You should also format the cells where you typed the two dates as Date in the
Number tab using maybe the 3/14/01 format. Right now your dates are formatted
as Mar-01 (mmm-yy).

 

[smooney]

still not working...but thank you for trying. I'm giving up for now....

 

[TRYING]

Sorry it's not working for you, but I believe it should work. I'm sure there
are better ways of explaining this than I have done.

Try this if you want to pursue this further.

A B
C
1 Type 12/31/07 then enter
2 Type 3/31/07 then enter
3 Type =A1-A2+1 then enter

Select cells A1 and A2 and right click.
Select Format Cells.
Click on Number tab.
Select Date.
In the Type box, select 3/14/01 and click OK.
Right click cell A3.
In Number tab, select General and click OK.
You should see something like the sample spreadsheet below.

A B C
1 12/31/07
2 3/31/07
3 276

OPTIONAL:
In cell B1, type = then point to A1 and hit Enter.
In cell B2, type = then point to A2 and hit Enter.
Copy the formula in cell A3 to cell B3.
Select cells B1 to B3 and right click.
In Number tab, select General and click OK.
You should see something like the sample spreadsheet below.
This shows that 39447 is the numerical equivalent of Dec 31, 2007 and 39172
is the numerical equivalent of Mar 31, 2007.

A B C
1 12/31/07 39447
2 3/31/07 39172
3 276 276

I hope the extra information does not confuse you. Personally, I find it
good to know why I can do math with Excel dates.

 

[smooney]

Thank you again....but this is what I get (using your first example below)
and I have followed your directions exactly. Grrrrrrr....so frustrating.
Thank you for your patience.


A
1 12/31/07
2 3/31/07
3 #VALUE!

Also tried the optional one and I cannot make those dates show a numercal
version of the date. I've tried everything. By choosing general it still
shows as a date.

I'm starting to think I'm mildly challenged....lol.

 

[TRYING]

The heavy hitters might come to the rescue soon. I only know enough to be
dangerous. Just guessing that there might be some global settings that affect
your spreadsheets.

On my own, I even tried typing 12/31/07 in A1 preceded by an apostrophe
(thinking that Excel would treat this as text). I did the same with 3/31/07
in A2. Then I typed the formula =A1-A2+1 in cell A3 expecting some error
message, but cell A3 still gave me 276. So even if I try to mess it up, it
still works for me.

I would be frustrated too if I were in your situation.

 

[David Biddulph]

If you're getting #VALUE! error then presumably your input cells (or at
least one of them) are being regarded as text. I guess that this is because
your Windows regional settings are expecting dates to be in the format
31/12/07, not 12/31/07. Either change the order in which you type the data
into the cells, or change the regional settings in Windows Control Panel.

 

[smooney]

Thank you...this works. However, I was hoping to use existing cells with
dates in them but if this is how I have to do, then so be it.

Thank you again!

 

[smooney]

Yep...it was the order. When I enter the dates with day/month/year and then
format them to 3/14/01, everything works great.

Thank you very much...all of you!

 

[T. Valko]

That should work with cell references. If you're getting #VALUE! errors I
suspect your date(s) are not true Excel dates. They may be TEXT strings that
look like dates.

Here's an alternative.

A1 = 3/31/2007

=DATE(YEAR(A1),12,31)-A1+1

Format as GENERAL or NUMBER

 

[Harlan Grove]

Here's an alternative.

A1 = 3/31/2007

=DATE(YEAR(A1),12,31)-A1+1

....

Simpler as

=DATE(YEAR(A1)+1,1,1)-A1