Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Spreedsheet in Excel

Posted on 2014-11-05
12
Medium Priority
?
320 Views
Last Modified: 2014-11-06
Hello,

I need some help creating a spreadsheet in Excel that give me automatically the total sum as soon as I add money to it.

I would like to create 4 different columns.

1) Days of the week
2) AREA 3
3) VIC
4) PCs

I would need this spread sheet created per week first then per month. On topo there should be a row to type the month of the year.

I am attaching a drawing of what I am trying to accomplish.

Thank you everyone.
0
Comment
Question by:Italiabella
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40424099
No drawing attached.
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40424446
Cobbled something together.  

It only does by Week now, but could be expanded to do months - would need to probably use the EOMONTH formula there though.  

Is this close to what you want?
M--ee---SUMIFS.xlsx
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40424453
note, dates have to be entered in a proper format that Excel recognises - ie dd/mm (for the UK)
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:Italiabella
ID: 40424685
I am sorry, here it is ...
IMG-3343.JPG
0
 

Author Comment

by:Italiabella
ID: 40424700
Very Very well Dan, you understood perfectly my broken English.

Can we add a 4th colums please, I don't have a name for it yet. AND the Month on the year on top of it like it shows in my dwawing...

In one spreadsheet I would like to have the 5 weeks. Therefore, one month.

thanks,
0
 

Author Comment

by:Italiabella
ID: 40424986
Phillip can you see it now ?
0
 

Author Comment

by:Italiabella
ID: 40425520
Hello Everyone,

I have been doing some work on the spreadsheet that Dan provided today. I organized by week.

When I showed to a friend he told me that the look is very poor and basic. I said that it works and it will help me to monitor each week how I do.

Does anyone can improving the look or the organization maintaining the meat of the spreadsheet. I mean, I need to have still the 5 weeks, the 3 income coming form Area 3, VEC and PC and the total of each week and the total of the month.

I am attaching what I did so far..

thank you everyone
November--2014.xlsx
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40425825
Will try and look at this a bit later... Is your Week 1 the first Mon-Fri of the month?  

And, would you then over-run into the next month eg:

Mon 29/12
Tue 30/12
Wed 31/12
Thu 1/1
Fri 2/1
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40425881
updated, but there's not much cleverness happening here any more, except that the dates roll forward when you enter the Month date.

Haven't thought of a nice way to have it STOP putting dates in when there's only a 4-week month, but there you go... typical month would be July '14
M--ee---calendar.xlsx
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40425883
Cell B7 does the calc for the first Sunday of the Month.
0
 

Author Comment

by:Italiabella
ID: 40426446
Hello Dan,

Thank you for adding the 4th colum, it will be for "Extra" revenues.
I work 7 days a week. The first week as a mistake as I forgot to start the week with Saturday November 1st, instead I started the week from Sunday the 2nd.

Yes,  I would love to have a spreadsheet setup so that every month just entering the month of the year it automatically generate the dates from the 1 to the moth to the 30 of the month or the 31 of the month depending on which month we are talking about.

Now I think I understand when you say type the << Enter the date here, in full format, for the first day of the month that you want.            But it doesn't work. I tried to type December 2014 and nothing change. :) please don't laugh I am not familiar with Excel.. Can you show me what I have to type for the month of December in order to come out just fine please
What you did is great !

thank you so much.
0
 
LVL 23

Accepted Solution

by:
Danny Child earned 2000 total points
ID: 40427396
just enter 1/12/14, and it should go from there, for 1st December.  Even just 1/12 should work.

If you click on the "November" cell, and then look at the address bar, you should see the format there as well (it may be different for your location, as different countries show the dates in different ways).  All I've done here is to have a normal date, but to change the format of it to mmmm yy - ie Full name of month, then 2 digit year.

To change to a Saturday start, change the formula in B7 to be
=DATE(YEAR(B2),MONTH(B2),1+7*1)-WEEKDAY(DATE(YEAR(B2),MONTH(B2),8-0))

The final 0 changes the start date.
0=Sat, 1=Sun, etc.

Here's more info on how Excel works with dates:
http://www.cpearson.com/excel/datetime.htm
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question