weekly & monthly formulas

ecox2000 · Apr 22nd, 2002, 11:57 AM

is there a way to make a worksheet total number from sunday-saturday while at the same time totalling the 1st of the month through whatever the end of the month is without having to create new sheets each time?

Mark W. · Apr 22nd, 2002, 12:03 PM

What's your data look like?

ecox2000 · Apr 22nd, 2002, 12:11 PM

Quote:

On 2002-04-22 11:03, Mark W. wrote:
What's your data look like?

i have to create a spreadsheet that allows a user to enter dollar amounts & quantities on a daily basis (sunday-saturday). then i have to add up each of the values for a weekly value. this i've got, but i'm not sure how to go about having the spreadsheet calculate the monthly figures (since sunday isn't always the first of the month).

say i have data where the total dollar amount spent on labor is entered for each day of the week. i just add sunday through saturday, but how do i apply the same logic, if the date (1st of the month) is entered for the specific day of the week to tally up the monthly total spent on labor (without having to enter the data twice). maybe having separate sheets or something.

Rocky E · Apr 22nd, 2002, 12:12 PM

Well as Mark has said without some more to go on it is tough to say anymore than it is done all the time.

Look up DAY, WEEKDAY, MONTH, YEAR .etc in the help section. with SUMIF or an array formula they will pull the data you require.
Month for example will allow you to select all the days of a month no matter when it start or how many days ar in the month.

HTH

Rocky...

[ This Message was edited by: Rocky E on 2002-04-22 11:30 ]

Mark W. · Apr 22nd, 2002, 12:18 PM

PivotTables would provide a flexible solution to your request. Do you have any interest in using them? If not, you could use DSUM along with a appropriate critera. I trust that your list of values has field names.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 22nd, 2002, 11:57 AM	#1
ecox2000 New Member Join Date: Apr 2002 Posts: 2	is there a way to make a worksheet total number from sunday-saturday while at the same time totalling the 1st of the month through whatever the end of the month is without having to create new sheets each time?

Apr 22nd, 2002, 12:03 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	What's your data look like?

Apr 22nd, 2002, 12:12 PM	#4
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Well as Mark has said without some more to go on it is tough to say anymore than it is done all the time. Look up DAY, WEEKDAY, MONTH, YEAR .etc in the help section. with SUMIF or an array formula they will pull the data you require. Month for example will allow you to select all the days of a month no matter when it start or how many days ar in the month. HTH Rocky... [ This Message was edited by: Rocky E on 2002-04-22 11:30 ]

Apr 22nd, 2002, 12:18 PM	#5
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	PivotTables would provide a flexible solution to your request. Do you have any interest in using them? If not, you could use DSUM along with a appropriate critera. I trust that your list of values has field names.