I have a spreadsheet which I use to track when a work request is recieved, when we confirm the request and when we action the request. I have been trying to write some code to count the amount of requests, receipts and actions we have processed in the last month.
My first column shows who the request is from
The second shows date recieved
The third shows date we send receipt
The fourth shows the date actioned.
I'm sent a file every day where column A contains a month & year but in "general" format.
For example;
Column A, starting with cell A1 with the next date in A2, etc
Jan15
Feb15
Mar15
Apr15
Is there a way through a formula or macro that I can use in order to run through all the dates in this column to make sure that the next cell down is always the month after the cell above it? The dates go all the way through to 2018 in the above format.
Need to be able to highligh data within a range where the date in a column is for the previous month/s- therefore need to be able to also include in macro entering the current month - don't want to have to edit macro each month
View 2 Replies View RelatedI have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically?
I enter in Cell 1: 1/31/09
Excel calculates in Cell 2: 12/28/08
I enter in Cell 1: 2/28/09
Excel calculates in Cell 2: 1/31/09
How to write a VBA code so that it would calculate the date of the previous Friday and from there Return the date exactly 1 month before; on any given day.
For example...
today is may 8th
So if I run the code the dates returned for last friday should be 05/02/2014
and 04/04/2014 should be returned AS the DATE exactly a month from 05/02/2014
Trying to word this right. I have one cell with a date of 01/01/2010. I have other cells that I want to be equal to this cell plus 1 or more months.
For example A1=01/01/2010
I want A2 to = 02/01/2010 based on one calendar month entered into A1. So if A1 changes 03/01/2010, A2 will = 04/01/2010.
Can I modify this forumla to total for the whole month rather than just a single date:
Here is the formula:
=COUNTIF(INDEX(D:D,10):INDEX(D:D,500),A4)
So if A4 is "01/04/09" I would like to count for the whole of April, not just the 01/04/09.
I have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.
I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.
Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.
I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.
this was the macro that was created
Code:
ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "3/10/2014")
Range("A59").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"
Range("A60").Select
I have a a set of date ranges. The start and stop date of the ranges are listed in seperate columns. I need to count how many of those date ranges include a specific month/year. Example data is below.
Start
Stop
Month/Year
[Code].....
I have attached a copy of a spreadsheet that I am putting together, What I need to achieve is, when a date is entered into the completion date cell the daily count down in the days left cell stops but still shows the value in that cell. The Annual or Monthly cal tabs are the ones i need the formula for.
View 5 Replies View RelatedI have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. Any way to identify the number of days, per calender month, that falls in a date range.
sample data...
Start Date
End Date
Old Value
New Value
08/03/2010
18/06/2010
16758.2
16758.1
[Code] .......
I need to break down the total number of days per month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
24
30
31
18
[Code] ......
As you can see this also laps into a new year, which poses my next problem, ill probably just add more columns on to the end of the table for that though...
I will later apply different calculations to these cells but in short need to get a calculation for the number of days per month first.
(in short spreading the new value out accross the year then multiplying it by the days... i also need to apply a further daily volume cal to it).
I need a VBA code which tells me the date of previous monday of any date. The user types in any date to the cell A1, and I need the date of previous monday to be inserted to the cell A2.
For example:
A1: 9/5/2009 --> A2: 4/5/2009
A1: 19/5/2009 --> A2: 18/5/2009
A1: 27/12/2009 --> A2: 21/12/2009
I have a column with a few dates.
I need a formula to display the previous date to the most current date out of the list.
How do I do that?
I need assistance with the following date range scenario: I have a date field where the user enters a date. I want the next field to automatically give the month of that date for example: user enters 01/01/2008 I want the next field to automatically say "JAN" etc.
View 5 Replies View RelatedI have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.
It is a monthly reset so it has to roll on from the previous month.
Here is what i have currently but it isn't working.
Code:
' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then
[Code]...
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
View 2 Replies View RelatedI have a list of clients that have specific requests waiting to be actioned, with a number of columns relating to client details and the status of the request across the top of the sheet. One of the columns (D) is "Last Contact Date", where I enter in the date that I last followed up with the client or made contact regarding their request. In the next column (E), I want the weekday/workday date 1 month after the Last Contact Date.
I can use "=D2+DAY(30)" to give me the date 30 days later, or "=DATE(YEAR(D2),MONTH(D2)+1,DAY(D2))" to get one month later, however what I want is the nearest WORKDAY after this date.
So, if the date is on a weekend, I need the Monday date instead.
I'd like to sum a range of cells if the date in the adjacent cell is last month, so... A2:A1000 holds the dates and B2:B1000 holds the values, i'm trying to figure out how to sum all of the values for the previous month (i.e. June).
View 7 Replies View RelatedI have a spread sheet for 12 months. I am wondering if there is a way to format to show in the cell only day of the month.
View 4 Replies View RelatedI need VBA code to sort the birthdays for all the employees in my company.
See the attached file for example.
I need to sort according to their month and then date and not with their year.
i.e it should not consider Year for sorting.
I have a column with dates populated(examp.Fri, 15 Feb 08)in it. In the next column I need to return the month and year (month,year format) this specific date falls in?
View 9 Replies View Relatedget 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 have a large excel file with the top seven rows such as you see in the attached file. In row five there is a bunch of info before the month and the year. What I want to do is get rid of that info besides the month and year. I than want to sort each column by the month and year so that all the columns are in sequential order.
demo.xlsx
Count the cells A10:A2000 that contain the word "Acomb"
and also
Count the cells B10:B2000 that contain the date "01/04/07"
I only want a value returned where both appear on the same row.
(e.g: A17 contains Acomb, B17 contains 01/04/07 - count, but if A28 contains Acomb, and B28 contains 07/04/07 - disregard)
I have 2 sheets in my workbook. On the first sheet (KPI) I have a cell with a date in, this is always the first date of a month. On the second sheet (MCR) I have all my data.
I am trying to write a formula (which is loacted on the front sheet [KPI]) to count the number of cells that contain a date that falls on or after the date on the front page, as such I have the following:
=COUNTIF(MCR!S2:S200,>= F8)
Where F8 is the cell with the date i.e 01/05/2008
When i run this I get an error, it appears that the formula is trying to look for cells that contain the date "F8" instead of the date,
I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.
I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.
I've attached an example work book and I would like the result in H2.
Example Workbook.xlsx
I would require a formula to count the amount of workdays between cell a1 and cell a2. Assume formula is in celll a3 and both a1 and a2 are date formated.
View 2 Replies View Relatedgive me the formula to count the number of rows entered within a date range. i.e. how do i count the number of rows that contain dates in june 2013
View 6 Replies View Related