Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 683
Default today as a function of the year to date

I need to create a formula that will automatically calculate the number of
tickets sold per month that takes into account the current date. The number
of tickets sold since July 1, 2007 is in a cell which is continuoslu updated.
As of today, October 9th, we have sold 5251 tickets which will result in
around 1595 tickets per month. But this sure has me stumped, any ideas?
--
Brian
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default today as a function of the year to date

Assume the number of tickets sold is in A1, and in D1 you have the
reference date (1st July 2007) in Excel date format. This formula will
give you the number sold per day:

=A1/(TODAY()-D1)

If you take it that an average month is 30 days, then multiply by 30
to get the monthly rate.

Hope this helps.

Pete

On Oct 9, 4:44 pm, Brian wrote:
I need to create a formula that will automatically calculate the number of
tickets sold per month that takes into account the current date. The number
of tickets sold since July 1, 2007 is in a cell which is continuoslu updated.
As of today, October 9th, we have sold 5251 tickets which will result in
around 1595 tickets per month. But this sure has me stumped, any ideas?
--
Brian



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default today as a function of the year to date

=A1*(365.2425/12)/(TODAY()-DATE(2007,7,1))

The 365.2425/12 term is what I have taken as the average number of days in a
month. You may wish to define a month differently.
--
David Biddulph

"Brian" wrote in message
...
I need to create a formula that will automatically calculate the number of
tickets sold per month that takes into account the current date. The
number
of tickets sold since July 1, 2007 is in a cell which is continuoslu
updated.
As of today, October 9th, we have sold 5251 tickets which will result in
around 1595 tickets per month. But this sure has me stumped, any ideas?
--
Brian



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 683
Default today as a function of the year to date

Thanks Pete and David, Davids works best but I ended up using a cell location
instead of the date(2007,7,1) for last years year end. I tip my hat to you
both.
--
Brian


"David Biddulph" wrote:

=A1*(365.2425/12)/(TODAY()-DATE(2007,7,1))

The 365.2425/12 term is what I have taken as the average number of days in a
month. You may wish to define a month differently.
--
David Biddulph

"Brian" wrote in message
...
I need to create a formula that will automatically calculate the number of
tickets sold per month that takes into account the current date. The
number
of tickets sold since July 1, 2007 is in a cell which is continuoslu
updated.
As of today, October 9th, we have sold 5251 tickets which will result in
around 1595 tickets per month. But this sure has me stumped, any ideas?
--
Brian




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default today as a function of the year to date

I need to create a formula that will automatically calculate the number of
tickets sold per month that takes into account the current date. The
number
of tickets sold since July 1, 2007 is in a cell which is continuoslu
updated.
As of today, October 9th, we have sold 5251 tickets which will result in
around 1595 tickets per month. But this sure has me stumped, any ideas?


Here is another possibility... it calculates the actually number of months
passed prior to the current month and adds to that the fraction of the
current month that has passed to date (day of month divided by total days in
month) and divides that into A3 (where I am assuming your total number of
tickets sold is kept).

=A3/(DATEDIF(DATE(2007,7,1),TODAY(),"m")+DAY(TODAY())/DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)))

Rick



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
MAX figure within a date range as a function of today()'s date irvine79 Excel Worksheet Functions 6 February 20th 07 03:28 PM
Using TODAY function to return a date as text GH Excel Discussion (Misc queries) 1 December 19th 06 06:17 PM
SUMIF within date range as a function of today()'s date irvine79 Excel Worksheet Functions 8 August 6th 06 05:55 PM
=TODAY() function to fix the date starguy Excel Discussion (Misc queries) 4 July 14th 06 02:18 PM
Today() or Date() function help julisimo Excel Discussion (Misc queries) 8 January 3rd 05 04:19 PM


All times are GMT +1. The time now is 12:38 AM.

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

About Us

"It's about Microsoft Excel"