I want to run a SUB based on if a date in a named range ("last_date") is greater than a week and another if it is greater than a month.
Do I do this by subtracting "Last_date" minus current date to see if it is greater than 7 (or 30)? Any help is appreciated. I seem to always have difficulties with VBA/Excel and comparing dates.
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
View 9 Replies View RelatedI am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
The object is to automate a weekly time sheet based on daily information. The "Daily Time" worksheet contains all 365 days of the year. The work week runs Monday through Sunday. Daily time is posted to the "Daily Time" sheet from handwritten time cards. The Weekly Time is then automatically calculated for each person and each category based on the dates in the "Pay Period From:" field and "To:" field.
For example if 01/01/09 (Monday) is in the "Pay Period From:" field and 01/07/09 (Sunday) is in the "To:" field, then the "Weekly Time" sheet will total the hours posted between those two dates. In this manner there will be a continuous record of daily time in the "Daily Time" worksheet and the "Weekly Time" worksheet will reflect whatever dates are used in the two fields. I realize that because it is simply two dates, it is possible to show any combination of days.
Basically what I need is to be able to add the hours between two dates and obviously the dates will change on a weekly basis. EXAMPLE IS ATTACHED
I am familiar with Sum if Formulas, but never done one that includes a date as criteria:
Table below:
Sheet1ABCD2DateEmployeeQty31/1/2012
Employee1241/1/2012Employee2552/3/2012Employee1763/5/2012Employee41076/5/2012Employee5689
For Junuary 2012 Employee 1 Processed210For Junuary 2012 Employee 2 Processed5Excel 2003
Basically row 9 and 10 is what I am looking for. I need the formula to view in the month of January and total all QTY for employee 1.
I understand I have I have to do a formula for each month, so that is find since the results will be divided into 12 columns, one for each month.
I'm looking for a way to have a formula that adds to a number based on whether or not it's past a certain date.
So the starting number is in cell D1. A1:B4 contains dates and numbers to add to D on a certain date. In column F I want the formula to look at the date in column E and then make any additions to the number in D1 based on the date and the numbers from in columns A and B.
So as an example, 03/02/15 would be 90, since since we're adding 20 from 01/15/15 and 02/15/15.
A
B
C
D
E
F
G
1
01/15/15
20
50
12/28/14
[Code] ....
I have a worksheet of data I need to sum based on a monthly date range criteria onto a separate summary worksheet. Both are in the same workbook. I tried using SUMIF and SUMPRODUCT but can't seem to get the criteria correct when I add in LEFT into the argument for the date criteria "6/" or "06/". Here's where I'm at so far:
=SUMIF('2008'!E4:E149,(LEFT('2008'!E4:E149)="06/"),'2008'!I4:I149)
My table looks something like:
Log NoShip DateQty
000953/26/20085
000963/28/20081
000974/28/20081
000985/28/20082
000995/29/20081
001006/1/2008
001016/14/20084
001026/20/20083
The 6/1/08 entry is intentionaly blank to be filled in as the value becomes known and then the totals would of course need to be updated.
In A8 I have a number 1 - 5.
This identifies the weeks in a month 1 - 4 for four week months. The 5 is for a five week accounting month.
In A9 I have a day of the week it could be any day formatted as dddd
In A11 I have the 1st day of the month 01/01/2007 formatted as ddd/yyyy
I need a formulae that will look at the number in A8 i.e. 2
Then look at the day in A9 i.e. Tuesday
Then look at the month in A11 i.e. January
Based on all of that I want it to return the date of the 2nd Tuesday in January 2007 in B11.
In a five week month I need it to return the last Tuesday of the month.
Weeks per month are show in starting in D11 for January.
I have a cashbook in Excel, the first column of which (column A) has the dates of various transactions. What I want to do is sum another column (column J) where the costs of the transactions fall between two dates in column A eg. between 01/04/07 and 31/10/07. I would like to do this using VBA.
View 7 Replies View RelatedI am working on Project Planning, I need to create the Progress curve for our Project based on Weight Factor every month.
I need to create the Plan and actual curve based on the dates defined in particular activities. Once the activity progress is calculated every month based on cut off date then it will sum product with corresponding WF to get the progress achieved every month. Presently I am calculating the values every month and pasting the values in Chart data every month. I had created the S Curve and attached here.
Now my objective is to automatically calculate the values for the entire cut off date at one time (From Project Start to End). i.e I don't want to do any manual work in the Chart. I will update only the actual Dates in the file, then the curves need to get automatically updated.
1) I don't want to calculate the progress for individual activities, I would like to calculate the overall progress directly.
2) When I change the Cut off date, then the previous values needs to become static.
I would like to know the function/syntax for determining the due dates given certain date ranges.
See attached sheet for the example : due dates example.xlsx
I have a table with 'Product Rereference', 'Date', and 'Estimated Assets' Columns. I need to run a formula that will extract the number in my 'Estimated Assets' column, based on speicific list of 'Product Reference' numbers that I have filetered, and a specific date - 1/31/ of each year.
I've been trying to create a formula, however I can't seem to work it out.
I have attached a file, which should make things a lot clearer :P I am trying to make the data extract into the table to the right. I have filled in the first few columns manually, to show what I would like to formula to achieve.
I need to insert blank row/s between the missing dates and fill the missing date & corresponding employees name. Let me explain in detail,
I have a start date in Cell E1 and end date in Cell G1. Need a macro to verify the date range in Col A with these start & end dates and insert blank row for missing date corresponding to employee names in Col B. This should be repeat till last used row of the sheet. Attached sample(Before&After) workbook.
Both missing date and respective names to be filled in the newly inserted rows.
I have a spreadsheet I'm trying to make which will have various cells change color based upon the date entered compared to today's date. Here are the details of it, and what I've tried to do so far:
Various safety tickets that employees acquire have a 3 year expiry date on them. What I want to do is be able to enter in the issue date, and then have the conditional formatting color the cell that contains the date based on the following criteria:
Green = More than 6 months remaining
Yellow = Between 6 months and 30 days remaining
Red = 30 days until expiry
Black = Expired (I wanted to eventually add this one, but Excel 2003 was only allowing me to add 3 conditional formatting conditions, so I just went with the 3 colors. I'm getting Excel 2007 later today, so I might be able to add more with that)
Now here is what I have so far:
3 years = 1095 days, 2.5 years = 913 days, 2 years and 11 months = 1065 days
N3 = 913
N4 = 1065
Conditional Formatting on cell C21
Condition 1 - color Green
=$C21+$N$3>=TODAY()
Condition 2 - color Red
=$C21+$N$4<TODAY()
Condition 3 - color Yellow
=AND($C21+$N$3<TODAY(),$C21+$N$4>TODAY())
I'm still having trouble with this formula. I have a sheet, sample attached, that has a list of transactions, with customer name, date, cash-in & cash-out information that is listed down a set of rows. I'll have a tab for each year & the information will be seperated by months.
My other sheet is a monthly report that I will be printing. I have a seperate "section" for each customer & I want excel to fill the information from that customer based on the month. I tried using a index match formula & a vlookup formula in a helper column, but it doesn't bring out the info that I want.
Any easy formula to compare two columens of fixed dates and count how many of each fall within a certain range of each other.
So, for example:
Col 1 Col 2
20/1/11 21/1/11
20/1/11 31/1/11
18/1/11 24/1/11
Lets say col one is a start date and col 2 is an end date. Now say I want to count the number of times the end date falls within8 days of the start
I would have thought that something as simple as
Code:
sumproduct(--(a1:a3<(b1:b3+8))
would work, but clearly I am making a fundamental error, and no matter what I try I'm getting stuck.
I have a worksheet that lasts for a year that is something look like this:
Date Name
1/1 John
2/1 Erwin
3/1 Robert
4/1 John
16/1 Erwin
17/1 Erwin
17/1 John
In my report worksheet, I need a formula to count everyone's attendance from their participation dates based on week
Name Week1 Week3
John 2 1
Erwin 1 2
Robert 1 0
I have a column CF which has to calculate the due date based on start date in column X and end date in column Y.
This is achievable however; I have dropdown columns in CE which has below option.
Below is the requirement.
If user select "Should Pay" from drop down in column CE, then in column CF the due date has to be Start date + 7 days
If user selects "Not to Pay" from drop down in column CE, then in column CF the due date has to be N/A or Blank
If user selects "Has Time to Pay" from drop down in column CE, then in column CF the due date has to be Start date + 9 days
If user selects "Misc" from drop down in column CE, then in column CF the due date has to be (End date - Start Date)/8+7
if this can be attainable either via formula or VBA.
I have code that pulls data from multiple sheets in a folder and throws a time stamp in the row/col. The data can have duplicates when adding to the master sheet. What I would like is to delete the duplicate row that is furthest from the NOW date.
Criteria for duplicates are based off of Col A. Time stamp is in Col G.
Highlighted rows would be deleted:
Sheet1
*
A
B
C
D
E
F
G
1
Item
*
*
*
*
*
Imported
2
112
a
b
c
d
*
Apr-5-2013 08:53:19
[Code] ..........
Is there a formula that can count based on dates?
Here's the scoop.
I created a spreadsheet which I would like for it to track trainings based on days, I added an =COUNTA(A1:A100) formula which counts the amount of people trained but what happens is that it counts people trained all week long. I want it to be able to differentiate days and display the amount of people trained per day , Ex. 4 on Mon, 5 of Tue , 6 on Wed, etc
I have a spreadsheet with due dates in column X. I want to create conditional formatting so that cells turn red when the due date is less than or equal to 30 days away, but turn gray when the due date has passed.
I've accomplished the first step by using the following: =AND($X6
I need a macro to copy and sum from sheet1 to sheet2 based on dates like in mentioned below 2 examples
Sheet1 data ....
I am trying to use conditional formatting to color a cell based on a date. I have a date in cell "a1" as my base date (no formatting needed). I have dates in cells "a5:a10".
I need those dates (a5:a10) to change color based on the time from "a1".
Less than 1 year = no formatting.
Between 1 year and two years = yellow.
Over two years = red.
I have tried different settings / formulas in the conditional formatting option, but I can't make it work. Do I need to use visual basic to do this?
1. Start Date (Col. C) - assign date to respective position based on first cell that contains value greater than 0 or "". eg. at first cell with 1 in it, looks up to row 4 and assigns date include in that cell in same column.
2. End Date (Col. D) - assigns date based on last cell with value greater than 0 or ""....
I want to sum values in column "C" based on if they fall between and equal to two dates, dates in "A" and a varible in "B"
Column "A" a2-a32 dates of each day in Jan 09 31 days.
Column "B" b2-b32 letters either X Y Z randomly through range some cells may have a letter.
Column "C" Dollar value
in cell D1 Startdate in cell D2 Enddate
Formula located in D3 is the sum of all values falling between the date ranges and by one of the letters X. D4 Y. D5 Z.
How can I lookup the value depend on Which year in the period?. I have prepared clean data attached. I use date calculation to find how many days in the period and then divide by 365 day to count the year. But it doesn't work.
View 5 Replies View RelatedI want to add employee hours (flight hours) based on the calendar (I want of sum of hours for the last 30 days on a running calendar.
View 7 Replies View RelatedI'm try to lookup a range of dates in a table, and sum up the data values that is equal or less then the specified dates.
Please view the attached spreadsheet for example.
I am trying to create a list which will summarise information from a dataset depending on two input cells in which dates are inputted by the user. I would really appreciate it if you could have a look at the file I have attached and give me some idea as to what formulae I should be using!
It is important that the position of the output list remains where it is (as ideally I want to draw graphs using the information summarised in the output list.
If your confused now when you look at the file it should make a bit more sense.
I have a specific issue that I have not been able find in here or on the internet. I need to count entries in one column based on a date range using corresponding dates in another column.I found countif, countifs, dcount, etc but have not been able to get any code to work. I don't know if my excel spreadsheet attached correctly but I can explain it since it's a simple one. Column A is filled with report dates that generally increase from 10/1/2012 t0 9/30/2013. Column B is filled with the number of "AFIS searches." If an AFIS search was not conducted for a specific report then the cell is blank. I need to count the number of AFIS searches between the dates of 1/1/2013 and 3/31/2013.
View 9 Replies View Related