#1   Report Post  
xlnovice
 
Posts: n/a
Default date


I am creating a spreadsheet for a small business that has incoming cash
on a daily basis, and outgoing payments on a monthly basis, The
payments occur on the same date every month but what i want to be able
to do is create a formula that will automatically put in the payments
on a given day every month (each month being on a seperate sheet) I was
looking to create a daterange formula but connot seem to get it to work




=IF(TODAY()=(DAY(B2:I2)),TRUE,FALSE)

This is the formula i have used (B2:I2) being the date range ie

8/11/04 to 14/11/04




--
xlnovice
------------------------------------------------------------------------
xlnovice's Profile: http://www.excelforum.com/member.php...o&userid=16456
View this thread: http://www.excelforum.com/showthread...hreadid=278138

  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Sun, 14 Nov 2004 12:19:06 -0600, xlnovice
wrote:


I am creating a spreadsheet for a small business that has incoming cash
on a daily basis, and outgoing payments on a monthly basis, The
payments occur on the same date every month but what i want to be able
to do is create a formula that will automatically put in the payments
on a given day every month (each month being on a seperate sheet) I was
looking to create a daterange formula but connot seem to get it to work




=IF(TODAY()=(DAY(B2:I2)),TRUE,FALSE)

This is the formula i have used (B2:I2) being the date range ie

8/11/04 to 14/11/04



You need a somewhat different syntax, and also you need to enter an
*array-formula*

The array-formula:

=OR(DAY(TODAY())=DAY(B2:I2))

will return TRUE if the day of the month today is same as any day of the month
in the range B2:I2. Otherwise it will return FALSE.

To enter an array-formula, hold down <ctrl<shift while hitting <enter. XL
will place braces {...} around the formula.




--ron
  #3   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

Hi

Here is an example of somewhat different design - I made it on fly, so you
probalbly have to improve it a bit.

Let's start with empty workbook.

Create a sheet SetUp
A1="Year:"
Into Cell B1 enter the number of year, the book is responding to.
Define the cell B1 as named range Year
Year=SetUp!$B$1

D1="Accounts"
From D2 down enter account codes/names/numbers - i.e. some identifier about
who is related to payment. Accounts column must be contignous!
Define the named range Accounts
Accounts=OFFSET(SetUp!$D$2,,,COUNTIF(SetUp!$D:$D," <")-1,1)

Create a sheet Payments
A1:E1 enter headers
Selected, Account, Day, Income, Outcome
Format Account column (reasonable number of rows) as data validation list
with source
=Accounts
Fill columns B:E with payment info - select account, into Day column enter
the payment day number, into Income and Outcome columns enter according
sums. You can have both income and outcome on same row in case they have
same payment day number, but it isn't restricted to enter them on separate
rows too.
To Selected column I'll return later!
Define the named range PaymentsTbl
PaymentsTbl=OFFSET(Payments!$A$2,,,COUNTIF(Payment s!$B:$B,"<")-1,5)
Define the named range PaymentsSelected
PaymentsSelected=OFFSET(Payments!$A$2,,,COUNTIF(Pa yments!$B:$B,"<")-1,1)
PS! To avoid possible problems in future, sort the payments table Ascenfing
by Day column - and keep it always so.

Create a sheet MonthlyRep
A1="Month:"
Format cell B1 as data validation list with source
'January,February,March,April,May,June,July,August ,September,October,Novembe
r,December'
Define named range MonthX
MonthX=MATCH(MonthlyRep!$B$1,{"January","February" ,"March","April","May","Ju
ne","July","August","September","October","Novembe r","December"},0)

A3:D3 enter column headers
Date, Account, Incoming, Outcoming
A4="TOTAL:"
A5=IF(OR(Payments!$B2="",MIN(DATE(Year,MonthX,Paym ents!$C2),DATE(Year;MonthX
+1,0))TODAY()),"",MIN(DATE(Year;MonthX,Payments!$ C2),DATE(Year,MonthX+1,0))
)
B5=IF(OR(A5="",Payments!$B2=""),"",Payments!$B2)
C5=IF(OR(A5="",Payments!$B2="",Payments!$D2=0),"", Payments!$D2)
D5=IF(OR(A5="",Payments!$B2="",Payments!$E2=0),"", Payments!$E2)
Format cells A5:D5 with appropriate formats and copy them down for some
reasonable number of rows. When a month in cell B1 is selected, all payments
in this month with payment date <= todays date are dispalyed
Into cell C4 enter the formula
=IF(SUM(C5:C##)=0,"",SUM(C5:C##))
and into D4
=IF(SUM(D5:D##)=0,"",SUM(D5:D##))
where ## is the number of last row with formula below.

Create a sheet AccountRep
A1="Account:"
Format cell B1 as data validation list with source
=Accounts
Define the named range AccountX
AccountX=AccountRep!$B$1

Now we have to return to sheet Payments
Into Payments!A2 enter the formula
=IF($B2=AccountX,COUNTIF($B$2:$B2,$B2),"")
and copy it down at least for same number of rows you have filled the
payments table (but it'll be clever to have some additional rows ready).

Return to AccountRep sheet. Into range A3:C3 enter column headings
Date, Incoming, Outcoming
A4="TOTAL:"
Select cell A5 and define named ranges
SelectedCnt=MAX(PaymentsSelected)
Date1=DATE(Year,INT((ROW()-5)/SelectedCnt)+1,VLOOKUP(MOD(ROW()-5,SelectedCnt
)+1,PaymentsTbl,3,0))
Date2=DATE(Year,INT((ROW()-5)/SelectedCnt)+2,0)
Into cell A5 enter the formula
=IF(MIN(Date1,Date2)<=TODAY(),MIN(Date1,Date2),"")
B5=IF(OR($A5="",VLOOKUP(MOD(ROW()-5,SelectedCnt)+1,PaymentsTbl,4,0)=0),"",VL
OOKUP(MOD(ROW()-5,SelectedCnt)+1,PaymentsTbl,4,0))
C5=IF(OR($A5="",VLOOKUP(MOD(ROW()-5,SelectedCnt)+1,PaymentsTbl,5,0)=0),"",VL
OOKUP(MOD(ROW()-5,SelectedCnt)+1,PaymentsTbl,5,0))
Format cells A5:C5, and copy down for at least
12*(MaxNumberOfPaymentsForAccount) rows.
Into cells B4;C4 enter formulas for totals, like on MonthlyRep sheet.

When an Account in cell B1 is selected, all payments from start of year
until current date (or until to end of year) for selected account are
displayed.

NB! When p.e. payment date 31 is entered into Payments table, then for
months with less than 31 days the last of month is displayed as payment date
in reports!

When next year begins, you copy the worbook under new name, change the year
on SetUp sheet, Edit Accounts and Payments tables on necessitate, and are
done with it.

This setup works when payments lasts full year. When this isn't a case, you
have to add at least 2 columns (StartDate, EndDate), on Payments sheet, and
have to modufy formulas which return dates on report sheets - so that Dates
validity is checked.


Arvi Laanemets


"xlnovice" wrote in message
...

I am creating a spreadsheet for a small business that has incoming cash
on a daily basis, and outgoing payments on a monthly basis, The
payments occur on the same date every month but what i want to be able
to do is create a formula that will automatically put in the payments
on a given day every month (each month being on a seperate sheet) I was
looking to create a daterange formula but connot seem to get it to work




=IF(TODAY()=(DAY(B2:I2)),TRUE,FALSE)

This is the formula i have used (B2:I2) being the date range ie

8/11/04 to 14/11/04




--
xlnovice
------------------------------------------------------------------------
xlnovice's Profile:

http://www.excelforum.com/member.php...o&userid=16456
View this thread: http://www.excelforum.com/showthread...hreadid=278138



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
Date updates from worksheet to chart & changes date to a date series! Help!! Jayjg Charts and Charting in Excel 2 January 22nd 05 04:00 PM
Automatically enter date and time but only update once. PM New Users to Excel 3 January 21st 05 08:47 AM
Date issue between Windows and Macintosh version dlg1967 Excel Discussion (Misc queries) 4 January 19th 05 04:51 PM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM
How do I convert a Julian date into a regular date? Jessica Excel Discussion (Misc queries) 4 December 2nd 04 03:54 AM


All times are GMT +1. The time now is 02:41 PM.

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"