|
Dates
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 |
Dates
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: 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 |
Dates
sorry for the mistake use
=datedif(a2,a1,"d") hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Marcelo" escreveu: 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: 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 |
Dates
Thank you....but unfortunately it's not working for me. All I get in the
cell is: #VALUE! "Marcelo" wrote: sorry for the mistake use =datedif(a2,a1,"d") hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Marcelo" escreveu: 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: 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 |
Dates
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. "smooney" wrote: 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 |
Dates
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: Thank you....but unfortunately it's not working for me. All I get in the cell is: #VALUE! "Marcelo" wrote: sorry for the mistake use =datedif(a2,a1,"d") hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Marcelo" escreveu: 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: 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 |
Dates
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" wrote: 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. "smooney" wrote: 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 |
Dates
Yes, format cell A3, if that is where your formula is, as General in the
Number tab. "smooney" wrote: 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" wrote: 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. "smooney" wrote: 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 |
Dates
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" wrote: 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" wrote: 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. "smooney" wrote: 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 |
Dates
still not working...but thank you for trying. I'm giving up for now....
"TRYING" wrote: 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" wrote: 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" wrote: 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. "smooney" wrote: 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 |
Dates
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" wrote: still not working...but thank you for trying. I'm giving up for now.... "TRYING" wrote: 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" wrote: 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" wrote: 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. "smooney" wrote: 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 |
Dates
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" wrote: 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" wrote: still not working...but thank you for trying. I'm giving up for now.... "TRYING" wrote: 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" wrote: 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" wrote: 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. "smooney" wrote: 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 |
Dates
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. "smooney" wrote: 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" wrote: 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" wrote: still not working...but thank you for trying. I'm giving up for now.... "TRYING" wrote: 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" wrote: 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" wrote: 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. "smooney" wrote: 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 |
Dates
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. -- David Biddulph "smooney" wrote in message ... 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" wrote: 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" wrote: still not working...but thank you for trying. I'm giving up for now.... "TRYING" wrote: 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" wrote: 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" wrote: 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. "smooney" wrote: 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 |
Dates
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! "Marcelo" wrote: 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: Thank you....but unfortunately it's not working for me. All I get in the cell is: #VALUE! "Marcelo" wrote: sorry for the mistake use =datedif(a2,a1,"d") hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Marcelo" escreveu: 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: 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 |
Dates
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! "David Biddulph" wrote: 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. -- David Biddulph "smooney" wrote in message ... 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" wrote: 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" wrote: still not working...but thank you for trying. I'm giving up for now.... "TRYING" wrote: 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" wrote: 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" wrote: 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. "smooney" wrote: 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 |
Dates
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 -- Biff Microsoft Excel MVP "smooney" wrote in message ... 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! "Marcelo" wrote: 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: Thank you....but unfortunately it's not working for me. All I get in the cell is: #VALUE! "Marcelo" wrote: sorry for the mistake use =datedif(a2,a1,"d") hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Marcelo" escreveu: 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: 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 |
Dates
"T. Valko" wrote...
.... Here's an alternative. A1 = 3/31/2007 =DATE(YEAR(A1),12,31)-A1+1 .... Simpler as =DATE(YEAR(A1)+1,1,1)-A1 |
| All times are GMT +1. The time now is 05:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com