The worksheet will have a list of staff and the date they sat for a particular test. I need to be able to highlight in some way when for example 6 weeks has passed so that the staff member can be re-tested.
We want to be able to open this worksheet and see immediately any staff who are overdue for a retest. If they have undertaken such retesting we obviously don't want to see them red flagged or whatever any more.
I want to write a function which will calculate whether a certificat needs renewing TODAY or not. The validity of certificate is 12 months.
HTML Code:Â
A B C D
type of cert date test done valid or not?
fire 01/01/10
test 01/03/14
health and safety 01/03/14
asbesto 1/03/05
gas 05/05/05
electricity 01/06/13
validity 12 months
I want to make a calculation on a spreadsheet only if the date is a Friday. (weekly returns calculation) I can't seem to find out how to test the date to see if it is a Friday.
View 7 Replies View RelatedSuppose you have a dates mix in column A with year-month-day format, for example:
ABC12012-04-27Call to the Doctor at 9am
22012-05-13Chicago Concert
32012-04-14Buy ticket to theater
42012-05-03Take children to match
52012-04-07George birthday
62012-04-21Bring the car to inspection
7
And in Column C you want to get the first or the third date of a month, for example for April
Query below
Date
Sales
1/7/2011
500
2/7/2011
600
[Code] ........
I was given the above table in an interview and was asked to find the sum between two dates without using sum and sumproduct.
I am trying to develop a For Loop that will do countifs functions based on the criteria in the For Loop. Part of the countifs criteria is determining which values in a range are within certain dates. I have isolated the month and year data and am looping with for loops through the years (outer loop) and months (inner loop). The issue that I am running in to is how to place the month and year into the countifs in VBA: month/year. When I do these calculations in the spreadsheet it is easy to write for one of the criterias: ">=1/2013", but trying to do that same formula with variables is become much more challenging.
How could I write a countifs that uses variables to do the same as ">=1/2013"? In essence: ">=monthCount/yearCount".
Get the number of workdays between two dates in a VBA function. I tried simply using the Advanced Toolpak function NETWORKDAYS, but I get the error message "Sub or function not defined". I did reference to atpvbaen.xls in the Tools > References menu, so I don't know why Excel is not recognizing the function.
I used the following function to test it:
Function CalcNetDays(dInitial As Date, dEnd As Date)
CalcNetDays = NetworkDays(dInitial, dEnd)
End Function
Any reason the function is not working?
Also, I need to add the holidays to the function. The holidays are listed on a worksheet column. Do I simply add the range to the function, like below?
CalcNetDays = NetworkDays(dInitial, dEnd, Range("Holidays"))
I try to find the price between two dates . I used lookup function, but couldn't get the correct result.
See detail at attached file and explained what exactly wanted : rates.xlsx‎
I'v been trying to do this for a while now. I need a function that takes this date (which is not a set format in Excel, so has to be 'general'):
10.10.2007 00:00
10.10.2007 06:00
10.10.2007 12:00
10.10.2007 18:00
11.11.2007 00:00
...
...
...etc
I need a function that increments this type of date format in a column.
I need a formula that can count items that are past due by up to 30 days, 60, or 90 days. I'd guess the total of all 3 values should = the total past due.
I have the total past due as =COUNTIF(A!I$1:I$278, "<=" & TODAY() )
i have tried: =VLOOKUP(TODAY()-7,A2:M2000,3) to return data that corresponds to a date 1 week ago. Unfortunatley, it does not work because my dates in the array are in descending order. The order cannot be changed... is there another function I can use to return the value I am looking for?
View 4 Replies View RelatedI have a list of all employees and the start date and end date they had an specific salary some dates overlap because they were working in different projects at the same time. I am looking for a function that will verify if any dates are overlapping for each employee. Some will have only one or two rows others have several rows. Here is the sample of the data?
RESOURCE ID
START DATE
END DATE
100000
12/26/1986
6/30/1994
[Code] .........
I have a contest that I'm trying to track.
Participants has 1 chance to win but can achieve the win in any of the 2 months.
I have attached a sample for a better explanation.
My issue is that I need to track only 1 win from either month 1 or month 2 from cells f & g. My formula works like so: If a YES is in cells f5: f8 it will calculate 45 days out from cells b5:b8 into cells g5:g8 or if a YES is in cells h5:h8 it will calculate 75 days out from cells b5:b8 into cells i5:i8. Right now I have the cells in g5:g8 & i5:i8 formatted to show just month and year with the column header titled " pay on the 15th of ", because I can't figure out how to have excel properly calcuate the 15th of each month from cells b5:b8 due to the various days in each month. If someone can help with this that will be OK too.
But my real problem is when there is a YES in cells f5:f8 I need cells h5:h8 to say "N/A" and cells i5:i8 to say "No Pay", because they can only have a win one time. However if cells f5:f8 say "NO" then cells g5:g8 will say " No Pay". Next cells h5:h8 will be evaulated for a "YES" and if a YES is in cells h5:h8 it will calculate 75 days out from cells b5:b8 into cells i5:i8. Last column j5;j8 will evaluate a yes from either cells f5:f8 or h5:h8 and if a "Yes" is found j5:j8 = "$25.00", if "No or N/A" is found j5:j8 = " N/A"
I have an order tracking sheet that has start times in column B, end times in column C and the processing time in column A. The start and end times are recorded in ddmmyy hhmm format (9/1/09 13:30).
On another sheet I have dates listed for each workday. Next to these dates I am wanting a formula/macro that will traverse through column B and provide the number of orders and average processing time that match this. I also plan on recording the shortest and longest processing time next to these.
I was calculating the number of days between two dates using the Days360 function. However, now it is not accurate enough for me... Is there a function that is more accurate? based on 365 days and includes leap years... that I could use as I need this calculation to be exact? For example, number of days between April 1st, 2010 and August 31, 2012 is 884, but the days360 function returns 870.
View 4 Replies View RelatedIs there a simple function for calculating the number of months between 2 dates.
Eg 1/5/2012 - 8/9/2020
i have 2 columns of number of cars using various roads, taken in the last 30 days. A macro has processed the data in these ranges and changed the outlier values to "Bad" so the formula ignores them.
However in the columns A and C , there are dates that the counts were taken on.
{=IFERROR(CORREL(IF(B4:B33"Bad";IF(D4:D33"Bad";B4:B33)));IF(B4:B33"Bad";IF(D4:D33"Bad";D4:D33)));"0")}
sometimes these dates aren't the same (no data available on christmas in some areas, boxing day in others etc. Obviously this destroys the correlation, as it puts the days out of synchronisation and the correlation falls apart.
Is there any way i can adapt the formula for it to check the dates are the same before correlating? It'd almost be like a vlookup, find the date, if match, add to calculation, if not then go to the next date. i'm happy for it to only correlate 26 of the 30 days if there are that many conflicts.
example for last 6 days performed on the 28th of december (looking for 6 calendar days up to but not including 28th dec.
22/12/2012 9 22/12/2012 Bad 23/12/2012 10 23/12/2012 22 24/12/2012 3 24/12/2012 21 25/12/2012 7 26/12/2012 18 26/12/2012 8 27/12/2012 15 27/12/2012 15 These 2 cells would be blank and this column would only contain values for 5 days.it could only correlate the 3 days of 23, 24 and 27.
Given that i'm going to be correlating this information with a number of locations, each with different reporting dates, filtering the dates before correlating and deleting unmatched dates is not a great option, but I will consider it if there is no other way.
Is there a command/formula that will automatically put the progressing date in the tabs of the sheets? Like if I am doing a spreadsheet containing 52 sheets for all the weeks of the year, is their a way I can label the first tab 1-1-2013, and then excel will auto label the remaining 51 tabs when I hold the control key, click/hold cursor and drag the sheet to make another? otherwise I am going to have to manually enter around 1000 dates for the tabs.
View 2 Replies View RelatedI know there have been posts regarding this before, but I can't quite get any of them to work. I'm trying to create a function which counts the number of working days between two dates. I've come up with the following code, but it doesn't work.
Function dayscount(Firstdate As Date, Lastdate As Date)
Dim x As Long
x = 0
For i = Firstdate To Lastdate
If WorksheetFunction.Weekday(i, 1) > 1 And WorksheetFunction.Weekday(i, 1) < 7 Then
x = x + 1
End If
Next i
daycount = x
End Function
I then use the formula "dayscount(A1,B1)", where A1 is my first date, and B1 is my last date.
I need the VBA/UDF to produce a column of dates between a start date and end date, excluding Sundays.
I can see lots, excluding weekends, but very little for just Sundays.
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedI'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011
control ~ (overview)
Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261
2nd view - system dates are getting converted into 1911
Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911
Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
Here is my set up:
A2 to BF2 is a range of dates
A3 to BF3 are sales. Days without sales are 0.00
I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.
Hi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.
I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.
I would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:
=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")
It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.
I have a tracking template with a column listing dates, all i want to do is find all the missing dates from that column of dates.
Example:
Column A
1-May
2-May
4-May
5-May
7-May
8-May
10-May
11-May
12-May
14-May
15-May
I want to list the missing dates from this list.
