date convertion into text

  • Thread starter Thread starter jv
  • Start date Start date
J

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
 
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.
 
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
 
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.
 
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
 
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.
 
Back
Top