I am trying to create an Excel spread sheet that keeps track of employees safety tickets and the dates they expire. Is there way to have a cell highlight to a color warning me 30 days prior to the due date listed?
I tried a conditional formatting as suggested in
[url] but that does not work.
I am using Microsoft Office Excel 2003 (MS Office Standard)(SP3)
I need a formula that will give a warning if the date entered in cell is a weekend day.
View 2 Replies View RelatedI am writing a roster spreadsheet and it has cells with drop down lists using validation lists to fill in the start and finish times, as we run a nearly 24 hour business it is possible to accidently start someone at 2.00am and finish them at 10.00pm. In the cell which calculates the time rostered I want it to display a messagebox if the hours exceed 10 hours or go into negative.
I need to "wife proof" this sheet as this stuffs up the total calculated hours and total costs.
Would I do this through validation on the cell with the formula calculating the times orr though a macro on the sheet module?
I am playing around with creating a calendar based on a date (month) I enter into a cell. I am doing this with a simple =date+1.
I have a sample attached. Only problem I am having is that for months with 30 days I don't want the last day to show the first of the next month. But I can't simply remove that as if I entered a month with 31 days I would need it to show the 31st.
I have a range of cells and I would like a warning message to pop up if a value over 0.1 is entered. I'm sure it is pretty easy to do, just can't find any info on here and I'm not great with Excel.
View 2 Replies View Relatedi wanted to know if there is a way which i can so that would tell excel if a certain cell value is lower then another it would give a msg
for example
Cell A1 - 5
so if cell A2 is less then five and a macro is clicked then the macro would give a warning msg hence ending the macro
I know the code is long, and bad, but I'm including it anyway in the hopes that it will give some clue. The activeworkbook.refreshall refers to a bunch of queries on other tabs of the workbook using VisualFoxPro ODBC if that matters...
View 9 Replies View RelatedTrying to do a linkback from another post located here but not having much luck doing it: [URL]
I'm working with 2 date columns and trying to filter a view to only include projects with dates within 3 months of today's date.
I've attached a current working file of the data and the end result i'm hoping to achieve via a macro of some sort.
I've manually got it to work via formula by inserting 2 additional columns (highlighted yellow) which determine if the dates "YES" fall in this 3 month time frame of "" blank if not.
create a macro which does all of this automatically without modifying any columns if this is possible
I want to have a warning message appear when a particular cell function result is a specific number, probably very simple to achieve.
View 9 Replies View RelatedI have a HR database that has user details on and successors so that if anything happens to a user (promotion or sick leave) then my database can identify who can step into that role temporary or permanently.
As i complete the cells at the end i would like to add a completed date then excel will count the days until it reaches a certain day and will change the text to either Review due in 1 week or review due in 3 weeks, 6 months review due, Overdue.
This way i can identify who i need to email so i can get up to date information on that role so if the users has changed position i can update accordingly...
hi i am currently trying to construct a date based maintainance sheet
i will try to explain what im trying to do
in one cell date is inputted
the next cell automatically adds +30 to generate next test date
what i am trying to do is get the sheet to
there are 4 testers so its a 3 month cycle for each ie
tester 1 jan
tester 2 feb
tester 3 march
tester 4 april
tester 1 may
tester 2 june
you get the picture? i hope
so if
cell A1 todays date
a2...................................b2......................................c2
21 jan.............................21 feb................................tester 2
inputed ...................... =A1+30
and date
formated
cell
i am trying to get the formula to tell me after the inputed date has been changed who the next tester is
i have tried using the IF command but it wont let me specify a date range ie 1 jan to 31 jan (or in the specified date codes) date range only one specific date with >= or <= which doesnt quite work properly
I have a start date, and an end date for a promotion. However, I am trying to create some unique keys for a VLOOKUP, so I can combine the data I have by day, with the data I have for promotional start/end dates.
So, simple question, if I have a start date and an end date, is there was way (either using excel or vba) to pull out all the dates within that range and display them on a spreadsheet?
For example, I have 21/01/2012 and 28/01/2012 as the start and end dates. I would want to display 21/01/2012, 22/01/2012, 23/01/2012 etc.
Is there a formula that can be used to convert a manufacturing LOT Number to a Date? For example, the first six digits of our Lot Number are the MMDDYY followed by an item description (120913DKP-01). We are trying to pull the 120913 portion out of the lot number to equal a Date of Manufacture of December 9, 2013.
View 4 Replies View RelatedWhat I have is a double quoted, comma delimited text file that is created from an external application, every now and again we need to open the file to make corrections and this is easier if we open it via Excel using the delimiter options, the problem is when saving the file as a csv it removes the double quotes, however I have found some vba coding that adds the double quotes back, which works great, but it changes the format of the date fields from what the original file had from 09-Aug-2009 to 09/08/2009.
I suspect this may be because the file is opened before the double quotes are added and excel changes the format.
Is there any code I can add that will stop the date format from changing ...
I am using the VBA Chart Control and I have the following instruction;
Me.ChartSpace1.Axes(ChartAxisPositionEnum.chAxisPositionCategory).Numb erFormat = "mm/dd/yy"
The chart is a "chChartTypeLine". the problem is that i cannot change the format of the X-Axis to a Date (mm/dd/yy) format no matter what I try? I keeps wanting to keep it as "General", here is the code;
Private Sub CommandButton1_Click()
Dim ser As ChSeries
Dim cht As ChChart
Dim MinValue As Double
Dim MaxValue As Double
MinValue = Sheet1.Range("D2").Value + 10
MaxValue = Sheet1.Range("A2").Value
I want to find the number of similar values contained in a column based on a date range I specify.
I have a data set that's formatted like the following (you can see the full data set at [URL] but I've hidden the irrelevant columns and highlight the relevant ones)
[Code] .......
I want to use this data to create two types of graphs. One showing the ratio including all values in the Status column. There are 4 total (Open, Lost, Won, Won-Job Cancelled). I want another which excludes the "open" status from the ratio figure. I've create some images demonstrating what I want to accomplish at [URL] .....
Additionally, I want to be able to specificy date ranges for these charts. Specifically 0-30 days from todays date, 30-90 days and older than 90 days.
I want to accomplish this using Google Sheets (the latest version) and know how to use the graph total. It's just creating the data the way I need it which is proving to be the most challenging.
I have a strange problem I cannot solve in that I'm trying to create an auto filter that returns records based on a date variable.
If I set the code to equal the date variable, it returns information. If I set the code to return records AFTER the date, it returns nothing.
Code to return records on the date:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="=" & startdate
This returns data, however if I slightly amend it to get records on or after the date:
Code:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=">=" & startdate
It returns nothing. Is there a syntax problem in my code.
Is there any way of creating a log that logs the last user that saved the workbook including the date and time. I would like the log to be a sheet hidden in the workbook. Not many people will be saving the workbook.
View 9 Replies View RelatedI have a lot of links on my excel page, links going to videos. Everytime I click the link, I have this message :
"there might be viruses... are you sure you want to open this file?"
I know there must be a way to take this message off : I worked with it for months, and then it suddenly disappeared. But now that I formatted, it's back again
I have several material takeoff sheets and one material SummarySheet in my workbook.
Each material takeoff sheet has a subtotal cell at the bottom of the sheet. The subtotals are added together and the total sum is displayed in a cell on the SummarySheet. At least that is how it is supposed to work. Excel or somebody else arbitrarily changed the cell reference in the SummarySheet formula to call up the cell one row above the subtotal cell on the material takeoff sheets. This little action resulted in a loss of $674,000 and may eventually result in my unemployment. Meanwhile I am sitting here putting out fires.
This is what I would like to do: Place a formula on the SummarySheet or add a Macro that will trigger a warning message box if the total on the SummarySheet is not equal to the sum of the subtotals on the material takeoff sheets. Also would like to have the message box animated or brightly colored.
If(PlateCostsTotals+AppurtenancesCostsTotals+StructuralCostsTotals+MiscellaneousCostsTotalsSummaryCostsTotal MsgBox “Hey, Don’t You Know How to Add”) or an alternate formula that works.
I recently added some code to close a file after a few minutes of inactivity. (Here's the sub code)
Sub time_out()
If Timer - LastEventTime
I am trying to put together a code so that when a user enteres more then two decimal points in the cell value they should get a warning regardless if they formatted the cell to show 2 decimals or not...
Simply the problem is on the sheet I have a lot of people format the cell to show two decimals but when you copy and paste the cell values to other sheets the decimals are still there which causes problems.
I am intermediete in VBA so if someone can shoot me some example codes I think I can make it work on my sheet.
I have a plan to create an excel sheet DB of softwares I sold to clients. Each license lasts 1 year. Here's an example of my sheet: ...
View 9 Replies View RelatedOn one laptop Excel crashes, without warning. It happens during copy/paste job (from one workbook to another). But not every time she copies something.
It is completly the same laptop like others in my company. My user has deadlines, and lot of copy/paste job.
I have an excel sheet there i am maintaining the details of our sales invoices. Invoice no is in the column D . My concern is while entering a new number in the column D, excel should look at the entire column and give an error message if the no is already exixting in the column.
View 9 Replies View RelatedI have an Excel file that contains macros that I made. Sometimes when I open the file I do not get prompted to enable macros. I DO want to get prompted so I can enable the macros. Any idea why this happens? By the way, my macro security is greyed out so I cannot change it.
View 3 Replies View RelatedRunning a worksheet with vba, at the start of the code I turn warnings off and at the very end of the code I turn them back on.When the routines complete a warning is raised:
A formula in this worksheet contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, range name, and cell reference.
When I run Error Checking everything comes up clean.
Clicking on "Show Formulas" shows no formulas for there are no formulas on the sheet to show, just a chart, and clicking "Show Formulas" just turns my dates into serial numbers and screws up the display of the two comboboxes, messing the fonts and drop-down arrows, and since closing and reopening didn't revert the comboboxes back to normal I will now have to blow them away and resurrect them. I also see Show Formulas also messed up my command buttons. nice...
The only way I find to avoid the error is to turn Warnings OFF at the start of the charting routine and NEVER turn it back on.
I made certain that none of my names had any errors in them. Everything looks proper. So what next?
I fear something will create an error for me now along the lines of Murphy's Law
Is there a way to make excel 2007 pop up a warning whenever calculation is set to manual by a macro or any other means? I have on several occasions noticed formulas not working, only to discover that calculation was set to manual without me noticing. And then I don't know how much of my work may have been afffected. This seems like a pretty vital piece of information, and I am surprised that it's not made more obvious.
View 11 Replies View RelatedI have a userform with a text box which asks for a Tag number which is then linked to a cell. Is it possible to show a warning dialogue box if a duplicate Tag number is entered?
how to set this up on a single cell also would like to see if code can be written for this?
For instance: Sheets(sheets.count).delete. I delete the last sheet, but I dont want a message box warning. Can i disable that with vba code? (offcource I can, but how?)
View 2 Replies View Related