Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.
View 2 RepliesIn a sheet I enter the following:
... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)
How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?
from a challenged Excel user who has a cell that needs to countdown the days of the month but need the ability to automatically remove Sundays. In other words the cell needs to refresh daily to show the number of days that have passed thus far and automatically know to not include Sundays. This would just be a numerical value.
View 6 Replies View RelatedIn attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
View 2 Replies View RelatedI have a worksheet called "zz_Exchange" that contains the exchange rate for my company for each month of the financial year. I am using this info in a summary page that uses the data for multiple calculations in the workbook.As we pass each month I would like to move the focus of a cell in the summary sheet to the relevant month in "zz_Exchange"
The column names are "currency_code" (USA Euro etc),
"currency_year" (09, 10)
"period" (1-12)
"exchange_rate" (this is where the data is held)
How to get only MONTH' YEAR if there is a DATE-MONTH-YEAR in a cell?
A1: 27-July-2012
Answer D1:JULY' 2012
Pl note "' " is suffixed after JULY (the month).
get other cells in a spreadsheet to populate with the Saturdays of a month based on the date input in another cell.
Example
A1 = October 2014
The following cells would automatically display:
A2 10/04/14
A3 10/11/14
A4 10/18/14
A5 10/25/14
A6 11/01/14
Then if cell A1 is changed to March 2015 the following cells would automatically display:
A2 03/07/15
A3 03/14/15
A4 03/21/15
A5 03/28/15
A6 04/04/15
I can't seem to find a way to get just the Saturdays. +7 doesn't work because Saturday is not always the 7th day of the month.
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsx‎
I want just the year to update because I look for information in other cells depending on the year. Also look for data based on just the month.
View 10 Replies View RelatedNeed to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies View RelatedI have created a time sheet in excel (see attached) that will be part of the larger workbook that will be linked with other sheets to auto fill in most fields. I am wondering if there is a way for an user to enter a Month and a Year at the top of the page and that in turn automatically fills in the days of the month by week.
So in attached sheet there are 5 boxes representing 5 weeks in a month. So if we used May 2014 as an example I would like to know if there is a way that once May 2014 is entered in up to top that. Excel fills in the dates in Week #1 with under Thursday showing 1st, under Friday showing 2nd as on for the entire month...
So as the month go by all user has to do is state the month and year and excel fills in the weekly dates for each day in month.
Attached File : Time and Attendance.xlsx‎
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
View 4 Replies View RelatedI'm after a formula this time ... i've searched the board and can't find what i need.
a cell shows 2009 December
and i'd like a formula to covert this to 31st December 2009 .... i.e. for any cell i'd like to know last day of month... and month and year ..
I have sheet1 want to populate current month all Saturdays dates in range C14 to C18 I am trying for vba or formula.
View 4 Replies View RelatedI use a certain portion of our workbooks to enter the due dates of certain equipment, which are always listed in month/year (e.g. 4/17), and which means that they are good until the last day of said month. That is, a due date of "5/16" is good through May 31, 2016.
I'd like for my users to be able to enter "5/16" and have that cell properly identify as May, 2016 (instead of May 16 of the current year)...more specifically, the last day of May, 2016. All of this so that I can conditionally format any cell where that date has past - that is, if my user enters "5/16" on May 15, 2016, it won't flag.
I just need to enter no. of sundays in a month against the month column for five years. Is there any way to get it by using formula?
View 9 Replies View Relatedcurrently I'm working as project manage for a software development studio and I am using MS project for all my work. Since the development team aren't aloud to have a MS project license on their PC I have to copy their tasks from MS project and put them into Excel.
I would say that I am okay using Excel but not an expert. I want to create a formula that uses NETWORKDAYS to determines a finish date from a duration and start date.
The headers I would use for each column are Duration, Start and Finish. So I would like to be able to get a team member to put a duration down i.e 1 day and give me the start day and in the finish date it produces a finish date automatically without including Bank Holidays and Weekends. What would the formula be for that?
I have the same question as Madball and was able to work out the Worday function, however for my company, we work on Saturdays and or Sundays sometimes.
I would like to be able to specify from line to line if Saturday or Sunday needs to be included in the duration calculation resulting in a finish date, however if I can simply get one formula to work that includes Saturdays alone (most common working day of the weekend, and do work more than don't) it would be the most helpful, I think I can get it adjusted from there.
is there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?
View 2 Replies View RelatedI am running Excel for Mac V2008.
My spreadsheet has various columns with column A representing dates of a 6 month period formatted as: ddd d-mmm-yy, one day per cell.
I want to highlight the cells which are Saturdays and Sundays and are not working days for my Plant.
I think it may be done using conditional formatting, but I have been unable to discover the right formula which would allow this to happen.
I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name.
Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?
i need a macro that when run will look in to a cell say A1 and see "august 2009" and will list in another sheet all the workdays for august 2009, And if possible, (i can do without this but it would be better) only the workdays (meaning skipping saturdays and sundays,)
View 9 Replies View RelatedI have been trying for the past four months now to get my workbook to automatically open a new worksheet every 1st of the month.
but I have failed each and every month. I have asked others to assist but to no avail.
this is the code in my workbook as it looks now.
Dim strValue As String
Dim Sht As Worksheets, foundSheet As Boolean, TEMPLATE As String
Dim rng As Range
Dim irow As Long
Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
Dim ws As Worksheet
Dim strName As String
Dim wsh As Worksheet
i keep getting an error on the activesheet=newsheet.
I have a worksheet that keeps a year to date and month to date total of overages or underages (end of day cash reconciliation). I need to have a formula that clears the month or year to date total cell at the appropriate time and I want to do this independant of the year as I generate a new sheet every day and enter yesterday's numbers.
I think I have the year figured out by picking the month and day and comparing but the only thing I have come up with for the month is comparing to a list of 13 dates (leap year).
I would like to see the Year To Date sum month by month in cell B8 when rolling down the cell B6. See in attached file.
View 9 Replies View RelatedI have created a spreadsheet for internal use in my workplace that logs task by members of staff, What I am trying to achieve is when someone selects from a drop down box that a task has been complete I want the task to automatically move to another worksheet for the relevant month, as I will have worksheets for every month logging all the past tasks that have been assigned. I believe an IF statement should do the trick, but I am having no luck so far.
View 2 Replies View Relatedi have attached a sheet, i want if i put date in cell a2 and automatically month should come in cell b2 and year in c2,
LIKE JAN AND 2009 LIKE THAT
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
Serial No Search E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4
E - Year Month Date
I need F column as Month Date Year Format
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here
In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now it’s 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.
View 3 Replies View Related