date convertion into text

[jv]

Good day to all,
I have a summary list of yearly sales from where i need to
come up for a certain formulas to reflect in some columns.

col. A B C
Date Day Sales
15-Dec-03 Monday 567665
16-Dec-03 Tues 597255
17-Dec-03 Wed 601235
18-Dec-03 Thurs 617345
19-Dec-03 Fri 617748
20-Dec-03 Sat 623345
13-Nov-03 Thurs 561583
14-Nov-03 Friday 561675

From the above sample, I need the ff:

1 - Convert col.A to appear the equivalent day in column B.
2 - I need to extract all "Monday" sales.
3 - I need to have a formula to get a "total weekly sales"
from "Monday to Sunday" from a given month
4 - Formula for a range to get Monthly sales.

Any help will be much appreciated.

Thanks and regards.

jv

 

[Norman Harker]

Hi JV!

I have your date data in A2:A9 and sales in C2:C9

1. To Convert col.A to appear the equivalent day in column B:

=A2
Format > Cells > Custom Format and type in dddd

2. To extract all "Monday" sales:

=SUMPRODUCT((TEXT($A$2:$A$9,"dddd")="Monday")*($C$2:$C$9))

3. To get Total Weekly Sales:

Why not just put in weekly summaries against each Saturday input.
Alternatively put in a column with the week numbers of the various
dates and use that as the summing formula's criteria

4. To get monthly sales:
=SUMPRODUCT((TEXT($A$2:$A$9,"mmm")="Dec")*($C$2:$C$9))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[jv]

Norman,

Thank you so much for the tips, it helps a lot! Referring
to my query # 3 - there are 3 dept. supplying 3 different
sheets from where i come up with my own summary report, i
have to combine all these 3 sheeets transaction into 1
sheet, but the sorting fails to arrange the date
accordingly.

Let me be the first to greet you a Prosperous New Year!

jv

 

[Norman Harker]

Hi jv!

Regarding your weekly sales, I'd take a look at the WEEKNUM function
(Analysis ToolPak).

If you inserted a column that gave weeknumbers for each date, you
could then extract the totals for each week in similar ways to that
used before.

Happy New Year!
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[jv]

Norman,

Once again thank you for the advised, I already issued a
memo to these 3 dept. requesting them to add another
column in their sheet specifically for the weeknumber
effective this year 2004.

Surely, there's a lot more to learn from you guys. It help
us reduce the workload and minimize the working time.

Keep up the good work.

jv

 

[Norman Harker]

Hi jv!

When asking for weeknumbers you need to be quite specific as to what
system is to be adopted.

Excel supports, through Analysis ToolPak's WEEKNUM function, two
systems:

WEEKNUM returns the weeknumber in the year. The 1st week starts Jan-1;
the 2nd week starts the following Sunday (return_type = 1) or Monday
(return_type = 2).

There is also a plain vanilla system where Week 1 starts on Jan 1 and
Week 2 on Jan 8

Then there's the ISO2000 system where Week 1 starts on the Monday of
the week with Jan 4 in it. This year (2004) Week 1 starts on
29-Dec-2003

For your purposes, it doesn't matter which system you use as long as
all three departments use the same system.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.