I have found that periodically Excel is automatically reformatting everything in large workbooks to Date Format. Initially I save these workbooks with General, Number, Currency, Text, and Date formatting within the cells. Upon opening at a later time, everything is converted to Date Format.
View 3 Replies
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.
This thread is somewhat of a cross post, the cross post is here
[url]
The cross post relates to a MS-Word related question.
However, I do have the same question, just for MS-Excel.
Right now my Macro runs when my Excel application opens.
But when my users are Xing(closing) out of the Excel application is there a way to remove all VBA upon closure of the Excel sheet.
I am tyring to make my database as foolproof as possible and one thing I need to be able to do is run a macro when the excel dataform is closed. I am guessing this is a worksheet event, so this is what I put together but it does not sort.
View 6 Replies View RelatedHow to get the "Expected date and time of closure" in MS Excel 2007
Call No. ABCD
Login Date 2/15/2014
Login Tme 9:30 AM
Agreed SLA (Service Level Agreement) 1hr
Accessibility Type 24hrs
Site Accessibility 24hrs
Accessibility Days All Days
ETC Date ?
ETC Time ?
[Code] ......
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
When doing vlookups, I frequently encounter an issue where one column isn't formatted the same and the vlookup won't work.
As an example I am trying to do a vlookup from a sql report downloaded to excel and the formatting on the column isn't right. If I type over the data, the vlookup works.
Is there a way to automatically clear the formatting so the vlookup will work.
This is my first post on this forum and I hope I'll find here all the help needed! This is VERY important for me. My boss is expecting me to finish this week. I am not really an expert in Excell but I have to do some pretty complicated things. At least they are complicated for me. J
OK ... here we go! ....
Private Sub txttodaysdate_change()
txttodaysdate = Format(Now, "mmm/d/yy")
End Sub
when i use this code i wnat the date to automatically appear in the text box but it doesn't I have type something into the textbox then the current date appears,.
I am working on a spreadhseet with multiple tables present on the sheet. Each table has various formatting, merged cells, formulas, and defined lists. I need to have the table add a new row below the last including cell formatting, formulas, defined lists, so I can just keep going through and do data entry. The code adds a row at the end so I don't run into the table below the present one, by monitoring how far away it is from the "Type" cell tag in column A. The code below is a compilation from viewing other threads, but it just does a copy and paste, including the data that was input so I'm looking at a duplicate row. How can I get ito to clear values in the new row? I can't seem to get it to paste xlPasteFormat and xlPasteFormulas
Is the new row in fact being added below the last? I think it's up one after the macro had run through.
Private Sub Worksheet_Change(ByVal Target As Range)
r = Target.Row
c = Target.Column
If c <> 1 Then Exit Sub
Application.EnableEvents = False
NextLineValue = Cells(r + 3, c)
If NextLineValue = "Type" Then
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).EntireRow.Copy
ActiveCell.Offset(0, 0).EntireRow.PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Application.EnableEvents = True
End Sub
I have to excel files
1. Temperature & Humidity Reading
2. Summary
I tried to copy a date from Temperature & Humidity Reading file using the formula ='[Temperature & Humidity Reading.xlsx]Sensor 7'!$C$2 to Summary file
if the date format is like this 6/10/2013 12:00:00 AM the result is fine, but when i try to copy that formula for the succeeding dates the results is same from what i copied.
How to set a column up to show it as the time of day but unless I enter PM after I put the time in the column it always comes up as AM.
Is there some way to just enter 9:09 in column d and or f and have it show up as pm? When I enter 8:10 in column b it automatically adds the AM to it but if I do the same in the column for the night and I do not type in pm it will automatically enter it as am.
I have attached the workbook and did delete the macro - which was just to clear all of my entries but when I click to open it again I am still getting the message about the macro.
In the workbook when I enter the time in column B I can just type in 7:14 and it will automatically add the AM to it. However, when I get to column D or F if I just type in 9:09 or whatever time it is in the evening it will automatically add AM to it unless I type 9:09 pm.
Is there some way to set it up so that I can just enter the time and it will automatically enter pm for everything in that column?
2013 sugar logs.xls
I have a sheet(Sample.xlsx) in which weekly data is collected and this is done by using the VBA code(Present in Copy 1 and Copy 2 button in Master US.xlsm) written by me. But in my code, it inserts a new after asking two parameters, i.e. line range(i.e. row no for eg A64) and date. I want it to automatically search row containing last date and inserts row below it and this needs to be implemented in "Insert Automatically" and "Insert Date" button using VBA code in Master US.xlsm sheet.I dont have problem with second paramenter.
Logic i want to use :
Itergation of whole page
{
If (col A<>Date)&& col A=Average(i.e. contains average word)
then
inserts row above that row
And then ask for date to enter using date variable.
then inserts row above that row containing last week data in col A.
}
"Insert Automatically" in Master US.xlsm sheet is the button which takes system date and implements the above logic.
"Insert Date" in Master US.xlsm sheet is the button which ask for only date and implements the above logic.
Sheets attached.
Master US.xlsm
Sample.xlsx
I have figure A1 column which total amount accounting at cells A31. I need figure 360 as a benchmark calculation perfectly been done. In this calculation 360 well off inside cells A18 although actual number is 363. If we use Conditional Formatting when had enough total in cells the achieve 360, cells colour will be transformed to red. What formula which need I used to change no matter cells on A1 to A30 if sufficient study 360 automatically cells changing colour to red
View 5 Replies View RelatedI'm building a Excel 2003 workbook that needs more than 3 conditional formats. I've tried the code given in the FAQ: Get Around Excels 3 Criteria Limit in Conditional Formatting but this only works on cells being modified directly. I've also tried the code in the post: VBA Conditional Formatting - Refresh when data changes but neither does the job I need.
I'm using a Format Control to present the user with a list of choices. When the control changes the target cell I want to apply a conditional format to that cell. This image shows the form. When the Format Control selection (in B2) is changed I want to apply conditional formating to the target cell (A2).
There will be up to 10 choices and therefore conditions.
"IN Next Sheet I want to show 28-Feb-2013 by this sheet by connecting to this sheet.
How can I do this? I have also attached a file for this in which i want to show last day of each month automatically whenever i put formula
How to order date automatically for leave balance that does not approved based on total days
View 2 Replies View RelatedI want each workbook to contain one month's worth of sheets, with a sheet containing one day's appointments. I need an easy way to name each sheet tab with the date, for example "01/08/09 Tue" for the first sheet and then all the following sheets will follow on date wise ie "02/08/09 Wed".
View 12 Replies View RelatedHere is what I want to accomplish:
I have a spreadsheet with data in cells A36 to G52. In The D column (D36 to D52) I have a date in there. I would like to have this column sort in ascending order automatically, but also have it sort the other data within the same row with it.
I have tried a couple of VBScripts that I have found in other posts that are similar, but I just get errors when trying it so I assume that I am not putting something in correctly.
Can anyone help me write this functionality? I have other data in a similar setup on the page that I also want to sort automatically but the script for this first part should be just a copy and paste, and change a couple of values....at least I am assuming so.
So, in column A I have a list of dates, I would like that in column B would be automatically inserted the date in column A + 60 days.
Example:
Column A | Column B
29.10.2011 | 29.12.2011
05.11.2011 | 05.01.2012
07.11.2011 | 07.01.2012
08.11.2011 | 08.01.2012
09.11.2011 | 09.01.2012
10.11.2011 | 10.01.2012
When I insert a date in column A the I would press TAB in column B would appear the date desired.
I made to automatically calculating by date which is time formating want to count the stem hours, but it prompted error?
Sub UpdateColG()
Dim IRow As Long
For IRow = 3 To Range("F").End(xlUp).Row
Range("G" & IRow).Value = Range("F" & IRow).Value - Range("F" & IRow - 1).Value
Next
End Sub
===================================
the logical is Range (F2-F1) returns to (G2)
sample : (F2)10:30-(F1)09:45 =(G2)00:45.
I'm looking for a way to achieve the following:
When entering data in any cell in a excel sheet I would like the previous date to automatically appear in particular cell so the cell will show the date I entered something in the cell. I want the date to be a fixed date, I don't want it to change.
On entering something in a different cell in the sheet, the date of the cell remain same.
After saving the sheet, the next time I open it the dates will still have to be the same as they were the first time.
How do I add month automatically on a date? if my begin date is 06/01/09 and I want to add 6 months to it how can I populate:
06/01/09
07/01/09
08/01/09
09/01/09
10/01/09
11/01/09
I have 2 Excel Files. In the main file I have a macro that copy/pastes data from the secondary file. The secondary file is a daily file that gets its file name changed every day (eg. 14OCT09.xls).
The last piece of the Macro goes back to the secondary file and ends with:
Windows("14OCT09.xls").activate
Range ("A1").Select
Now tomorrow, I have to manually edit the Macro and change the code to 15OCT09.xls before I run the Macro. Is there a way to automatically change this date without having to manually edit it on a daily basis?
I am trying to automatically add a date to a cell (D2 on sheet1) when there is data entered in a range of cells (C8:I59) on sheet2, so I can see the date of the last there was data entered.
View 3 Replies View RelatedThis code below is pasted from Automatically Add Date, Time or Date & Time and uses the Worksheet Change Event
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
With Target(1, 2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub
It automatically enters today's date into the corresponding B cell. How do I code it so that the date is automatically removed when & if I delete the corresponding A cell?
Each worksheet has 4 days on it.. and there are 4 worksheets per month ( Jan1, Jan2, Jan3, Jan4 ). I have this macro which runs ( dependant on password ) to lock all cells so they cannot be edited after. On this macro I have to enter the month to lock and it adds on the 1,2,3,4.
Sub LockCell()
Dim i As Integer
Dim ws As Worksheet
Const Pass = "password"
Dim InptPass As String
Dim Mnth As String
On Error Resume Next
InptPass = InputBox("Please Enter Password")
If InptPass <> Pass Then
MsgBox "Incorrect Password", vbCritical
Exit Sub
Else...
I'm trying to update an excel document which has a list of employees on it along with their training. Most of the training for individuals needs refreshed in the next year so wanted the table to use the date and perhaps trigger a fill colour change once they are close to needing that training. here's an example
John Smith
course 1
Completed 01/05/10
Refresher required 01/05/14
Once we get a month or two before the required date, I would like the fill colour to change to highlight that they refresher training should be organised soon.
Everyday I receive products that could be categorised into one of the five categories A, B, C, D and E. I enter these on a sheet (SHEET 1) along with an expected date of shipment, which could be any date between now and 12 months later.
In another sheet (SHEET 2), I have months in column A and each product type for each month in column B.
What I want is to write a formula in SHEET 2 so that every time an entry is made in SHEET 1, it automatically adds 1 to the respective cell in SHEET 2. For example, I received 10 products today (5*A, 2*B, 1*C, 1*D and 1*E). As soon as i made entry in SHEET 1, I wanted respective numbers to appear in SHEET 2.
I have made a daily planner for each month. The dates are on separate sheets which are titled 1st Dec 07, 2nd Dec 07 and so on.
This is also included on the actual sheet as well in the form of a heading. I want to know if it's possible to automatically rename the text on the sheet to what the sheet is called?
Or if the first sheet has a title of '1st Dec 07' how can I get the other sheets to automatically be titled in sequence so I don't have to do it manually?
