I'm having a problem with data validation. I set an entire column so that it could only be a date between the first date in the list and the current date. I tested a few cells to make sure that it was working the way I wanted and I noticed that it allows a random number like 3 or 5 to be input after row 50. This would create a date in the 1900s. Why is this happening and how can I stop it.
I am working on a spreadsheet to create a school 'tracking' system based on excel. What I need is that as soon as a box in column B is ticked, a date (TODAY() is entered in column A. That is not so difficult, and I have used the formula in cells in column A:
=IF(ISBLANK(Bx);"";TODAY()), where x is the specific row.
So, keep cell A clear untill something is written in cell B. This works fine.
But, for the purpose I need to keep the TODAY() date fixed after entry. And it need to be erased again if the tickbox in cell B is emptied again. It need then to re-enter a new date after cell B is re-used.
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies View RelatedI need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
In column A I have Dates
January till....
The code below count the number of months and then should create only, this amount of new tabs (based on monthlist array)
So if I had Dates from January till October it should create only 10 new sheets.
So far is entering all of them. I know I'm missing a loop somewhere
Code:
Sub AddSheets()
Dim lmax As Long
Dim lmin As Long
Dim lmonth As Integer
Dim cc As Integer
monthlist = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
[Code] ..........
I have a userform with 8 text boxes and users have to enter different numbers separated by commas like 1,5,6,15,28 and so on.
I want to put a validation that if a number is entered in any of the textboxes than the same shall not be allowed in any other textboxes. Even in the same text box, no duplicate number shall be allowed. ie if in a textbox 1,2,15 is entered , then in the same textbox also, user cannot enter 1 or 2 or 15 again.
I am trying to find out whether you can restrict the rest of the userform from being available, unless a certain field is entered.
I have a tax invoice userform, I want to restrict the user from entering any other data unless they have selected a customer.
There are other fields such as Customer Address, Customer Number, Disc% and Terms that populate through vlookup code once the customer is selected, so still need that to work once the customer is selected. I just don't want them to be able to enter any other data on the userform without the customer being selected.
I wasn't sure whether I needed to enter code into every other textbox or combo box that if cboCustomer = "" then pop up an error message, but that would require a lot of code to be entered. I have 30 comboboxes and 53 Textboxes that I want to restrict to not being available unless a customer is selected from combobox.
How can I restrict entry into a textbox to 1 decimal place?
View 9 Replies View RelatedI have a employee travel spreadsheet with air travel dates in two columns. I want to be able to create a rule that will highlight all the cells in a row if the date range on that row fall includes today's date. if someone is traveling in the event there is an emergency and I need to know if they are traveling that particular day. I have attached a sample spreadsheet.
View 3 Replies View RelatedHow do you restrict a user to only be able to enter up to 2 decimal places in a cell without it automatically rounding for them?
View 6 Replies View RelatedI am using the below code to enter the data in Cell "D" & "E" of the worksheet.
Dim a, b As Integer
a = WorksheetFunction.CountA(Sheet2.Range("C:C"))
ActiveWorkbook.Sheets("Retailing Data Sheet").Activate
'If Range("C" & a + 1).Value "" Then
[Code] .......
But, If someone wants to enter the data manually into the Cell "D" & "E" its allowing which i dont want.
It should be enter by using the form only...
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.
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date
Dim projEndDate As Date
Dim DateStart As Date[code]....
i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 4 Replies View RelatedLet's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.
If:
A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.
I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8
Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014
What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1
In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014
I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
Budget Picture.jpg
way to search and display a date from a range of cells based on less than or greater than criteria. For example I have following dates in column A:
A1: 2011/01/04
A2: 2011/02/01
A3: 2011/03/01
A4: 2011/04/01
I want to search for the date which is less than 2011/02/01 from the A column and display that date in B1 cell for example. How do I do that?
I want to do this without using any macros.
I have an 'existing results table' as per my attached sample.
I have had help previously from this forum to create lists of 'sold' stock within date ranges (tax year periods) and these are represented as 'sold list' in my attached sample.
I now need to create a list of 'unsold stock' for each annual tax year end date; i.e. populate my table with items that have been created before the end of the date range and that have not been sold by the end of the date range.
Please can someone show me the formula on my attached sample?
Please see my example in red.
I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
Is there a UDF that can determine the number of weeks for a date range specific that is not relative to the week number for the year but for the date range itself. i am aware of the weeknum function but this is for week number relative to the year. eg. date range 01/03/2008 - 31/05/2008 has approx 12 weeks and 14/05/2008 will be week number 10 for the range.
View 4 Replies View RelatedI've found similar queries on the site, but none seem to handle multiple dates, and my attempts so far have been futile!
Requirement:
I press a button on Sheet1 and am prompted to enter a start date and an end date.
Depending on the dates I enter, all rows on Sheet2 which fall outside the Start/End date range will be deleted.
All dates to be checked against on Sheet2 are in a single column, and all rows are populated, there are no blanks. Dates are in the format dd/mm/yy
I have a ComboBox on a UserForm. One of the fields that I fill in is a date.
After the date is entered I want to check if it's a valid date.
VB:
Private Sub cboEnterDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' Make sure a valid date was entered, it must be in the format 2013/10/21.
On Error Goto ERR
'If Not IsDate(cboEnterDate.Value) Then
[Code] .....
ERR:
MsgBox "The date entered is not a valid date", vbInformation
Cancel = True
cboEnterDate_Enter
End Sub
The code:
VB:
'If Not IsDate(cboEnterDate.Value) Then
execute the If routine if I enter the date 2014/03/33
When I enter the date 2014/03/0001 it do not see it as an invalid date and exit the routine.
The code:
VB:
If Not IsDate(FormatDateTime(cboEnterDate.Value, vbShortDate)) Then
send the execution to the ERR routine if I enter 2014/03/33
When I enter the date 2014/03/0001 it do not see it as an invalid date and exit the routine.
How can I get the validation to catch the 2014/03/0001 as an invalid date as well.
I am not sure how many questions I can ask in one day, but I have one more. I am using VBA code for easy date and time entry on a spreadsheet...I am a complete novice at this.
I need to add additional columns to the range for dates and times.
also, is it OK to use the code for date and time on the same worksheet?
Code for Date:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim DateStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("A2:A100")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Is there a way that I can set up two cells so that when a date is entered into one, the other renders something like "Reporting from XXXXX to YYYYY" where XXXXX is the Monday of that week and YYYYY is the Friday of that week?
So, for instance, if the user entered 1/14/09 into cell A1, then cell B1 would show "Reporting from 1/12/09 to 1/16/09"...
I want to type "121505" in a cell and it display 12-15-05. What formatting do I use?
View 4 Replies View RelatedI am trying to remove the duplicate entry of the date the list goes like this
01/22/2014 6:10
01/22/2014 6:12
01/22/2014 6:13
01/22/2014 6:15
01/22/2014 6:17
01/23/2014 7:00
01/23/2014 7:01
01/23/2014 7:02
01/23/2014 7:03
But I want
01/22/2014 6:10
01/23/2014 7:00
I have attached a file where I need to clean up column A but still maintain the rows sheet one is what I have sheet 2 is what I am hoping to achieve.
I have a workbook with several sheets, each of which represents a client. At the bottom of this sheet, I have a call log to record the times that we attempted communication, left a message, etc. Since it's tedious enough as it is, I'd like to save my team 2 seconds and a slight bit of that tedium by having the date an attempt was made automatically recorded in the row where the attempt is entered.
Let's say this all takes place in A47:D47. For example, if I called the client on 7/15/08 and left a message, I would record who was called in B47, the name of the caller (me) in C47, and the result in D47. If possible, I would like to have the date of the attempt (today's date on the given day) entered in A47 upon the entry of information in B47:D47.
I am attempting to create a formula which will detect of a cell is empty, and if it is empty enter the date by using TODAY(). I also dont want this date to change when opened after it has been entered. This is what I have been working with so far.
Code:
=IF(A1="",TODAY(),"")
Code:
=IF(G9>=TODAY(),A1=1,"")
I am trying to use the second formula to change the value in the cell "A1", but instead it just returns a true/false statement in the cell with this formula.