| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
to-date total based on current date
am trying to create a forumula that will give a cumulative or
"to-date" total that will exclude future months from the total. i.e. ithe point in time is august and I need to show a to-date number through august only even though september and october are included in the spreadsheet. Next month will need to show through september and exclude october. columns: july august september october year-to-date rows : 100 200 300 400 year-to-date as of august should show 300 if the point in time is august and show 600 if it's september is there a way to write a formula using time or that will change based on the current date? thanks for your help. Tina |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to-date total based on current date
Hi, Assume A1 is July date B1 is July Value A1 is Aug date B2 is Aug Value etc =SUMPRODUCT(--(--(MONTH(A1:A4))<=(MONTH(TODAY()))),--(--(B1:B4))) VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=569936 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to-date total based on current date
I didn't ge this to work. I put in ten months- 1-10 in columns A1:A10
and it still added all ten months vs giving me through august only. any suggestions? Thanks for trying! Tina VBA Noob wrote: Hi, Assume A1 is July date B1 is July Value A1 is Aug date B2 is Aug Value etc =SUMPRODUCT(--(--(MONTH(A1:A4))<=(MONTH(TODAY()))),--(--(B1:B4))) VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=569936 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to-date total based on current date
Try =SUMPRODUCT(--(A1:A13<=DATE(YEAR(TODAY()),MONTH(TODAY()),CHOOSE( MONTH(TODAY()),31,28,31,30,31,30,31,31,30,31,30,31 )))*(--(B1:B13))) May need to change the 28 for leap years VBA Noob -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=569936 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| running total by date (not sorted by date though...) | Excel Worksheet Functions | |||
| enter date(A) and have (B) total to current day? | Excel Worksheet Functions | |||
| get back to day one based on current date | Excel Worksheet Functions | |||
| NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
| Setting traffic lights based on todays date against target dates | Excel Discussion (Misc queries) | |||
Linear Mode
