Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
running total by date (not sorted by date though...) rainxking Excel Worksheet Functions 4 May 16th 06 02:01 AM
enter date(A) and have (B) total to current day? jeffcol777 Excel Worksheet Functions 3 February 2nd 06 07:14 PM
get back to day one based on current date oomyoo Excel Worksheet Functions 2 December 20th 05 08:46 PM
NETWORKDAYS - Multiple Date Selection Annabelle Excel Discussion (Misc queries) 3 October 4th 05 07:04 PM
Setting traffic lights based on todays date against target dates HDV Excel Discussion (Misc queries) 2 September 14th 05 12:05 PM


All times are GMT +1. The time now is 06:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"