Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
Sometimes I feel stupid. I've been trying to create a formula for hours and never thought to look for a forum. Is there a way that if I enter a date in say A1 as 5/4/06 that excel will then in A2 subtract that date from the end of it's month to give me the remaining days. But the formula needs to work for all months. So if I enter that date it will go to the month of may and subtract. But, my next date may be 2/4/04. So it need to work with all months. Is this even possible? Thanks for the help and I know I will have a lot more questions for everyone. -- boardmaker ------------------------------------------------------------------------ boardmaker's Profile: http://www.excelforum.com/member.php...o&userid=34854 View this thread: http://www.excelforum.com/showthread...hreadid=546014 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
Hi
Try this: =DATE(YEAR(A2),MONTH(A2)+1,0)-A2 with your date in A2 Format the cell as general. AndyB. "boardmaker" wrote in message ... Sometimes I feel stupid. I've been trying to create a formula for hours and never thought to look for a forum. Is there a way that if I enter a date in say A1 as 5/4/06 that excel will then in A2 subtract that date from the end of it's month to give me the remaining days. But the formula needs to work for all months. So if I enter that date it will go to the month of may and subtract. But, my next date may be 2/4/04. So it need to work with all months. Is this even possible? Thanks for the help and I know I will have a lot more questions for everyone. -- boardmaker ------------------------------------------------------------------------ boardmaker's Profile: http://www.excelforum.com/member.php...o&userid=34854 View this thread: http://www.excelforum.com/showthread...hreadid=546014 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
I just tried that with 2/2/2004 and I got 1/27/1900. Any idea why? -- boardmaker ------------------------------------------------------------------------ boardmaker's Profile: http://www.excelforum.com/member.php...o&userid=34854 View this thread: http://www.excelforum.com/showthread...hreadid=546014 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
format result as general NOT dates
Regards Peo Sjoblom "boardmaker" wrote: I just tried that with 2/2/2004 and I got 1/27/1900. Any idea why? -- boardmaker ------------------------------------------------------------------------ boardmaker's Profile: http://www.excelforum.com/member.php...o&userid=34854 View this thread: http://www.excelforum.com/showthread...hreadid=546014 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
Hy,
You can use function DATADIF to calculate the difference between two dates, now, for the formula month functions for every month would be interesting to have a table with the number of each and the last day of it, as eg. A B 1 01/31/2006 2 02/28/2006 .... 12 12/31/2006 Then the formula could be: assuming that you have the date on the A column and the table above on range C6:D17 =DATEDIF(A1,VLOOKUP(MONTH(A1),$C$6:$D17,2,0),"D") Hope it helps Regards from Brazil Marcelo "boardmaker" escreveu: Sometimes I feel stupid. I've been trying to create a formula for hours and never thought to look for a forum. Is there a way that if I enter a date in say A1 as 5/4/06 that excel will then in A2 subtract that date from the end of it's month to give me the remaining days. But the formula needs to work for all months. So if I enter that date it will go to the month of may and subtract. But, my next date may be 2/4/04. So it need to work with all months. Is this even possible? Thanks for the help and I know I will have a lot more questions for everyone. -- boardmaker ------------------------------------------------------------------------ boardmaker's Profile: http://www.excelforum.com/member.php...o&userid=34854 View this thread: http://www.excelforum.com/showthread...hreadid=546014 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date function
Hy,
You can use function DATADIF to calculate the difference between two dates, now, for the formula month functions for every month would be interesting to have a table with the number of each and the last day of it, as eg. A B 1 01/31/2006 2 02/28/2006 .... 12 12/31/2006 Then the formula could be: assuming that you have the date on the A column and the table above on range C6:D17 =DATEDIF(A1,VLOOKUP(MONTH(A1),$C$6:$D17,2,0),"D") Hope it helps Regards from Brazil Marcelo "Marcelo" escreveu: Hy, You can use function DATADIF to calculate the difference between two dates, now, for the formula month functions for every month would be interesting to have a table with the number of each and the last day of it, as eg. A B 1 01/31/2006 2 02/28/2006 ... 12 12/31/2006 Then the formula could be: assuming that you have the date on the A column and the table above on range C6:D17 =DATEDIF(A1,VLOOKUP(MONTH(A1),$C$6:$D17,2,0),"D") Hope it helps Regards from Brazil Marcelo "boardmaker" escreveu: Sometimes I feel stupid. I've been trying to create a formula for hours and never thought to look for a forum. Is there a way that if I enter a date in say A1 as 5/4/06 that excel will then in A2 subtract that date from the end of it's month to give me the remaining days. But the formula needs to work for all months. So if I enter that date it will go to the month of may and subtract. But, my next date may be 2/4/04. So it need to work with all months. Is this even possible? Thanks for the help and I know I will have a lot more questions for everyone. -- boardmaker ------------------------------------------------------------------------ boardmaker's Profile: http://www.excelforum.com/member.php...o&userid=34854 View this thread: http://www.excelforum.com/showthread...hreadid=546014 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date function in Excel that updates only when a doc is changed? | Excel Worksheet Functions | |||
Date Function | Excel Discussion (Misc queries) | |||
date format and the RIGHT function | Excel Worksheet Functions | |||
Date function | Excel Worksheet Functions | |||
Is there a function to show future date | Excel Worksheet Functions |