Excel 2010 :: Date Not Populating Cell

May 1, 2014

I have a userform which requires the user to enter the date a training course was completed. Initially I used DatePicker as I was running Excel 2010 and had that working, however the workbook I am creating will be used on about a dozen machines, all of which have different versions of Excel. To avoid needing calendar Add-ins to be downloaded and installed for this feature I decided to go the vba created calendar route. I am using one I found on this forum: [URL] .....

It works well for me using the example spreadsheet provided in the post but I am having some difficulty incorporating it into my code. I believe I have imported the appropriate forms and modules because I can get the date populate to the Label Box on my userform but when I try to put the Date into the spreadsheet along with other data such as name and course duration, the name and course duration populate but the date cell remains blank and execution completes without error.

I have tried playing around with the data type thinking I had some issue using the .Value attribute with a String but .Text gives me an error as does converting from String to Number format.

I will attach the workbook but for a quick look, the portion of the code I believe that is not working is in here:

[Code] .....

Training Classes Example Workbook.xlsm

View 3 Replies


ADVERTISEMENT

Excel 2010 :: Populating Field From Dropdown List

Oct 24, 2011

I am using windows 7, excel 2010 I have a work book, my price list is sheet one and my list of customers and what discounts they get per item in sheet 2.

I choose customer on sheet 1, say i choose a & L sales, well on sheet 2 it shows they get a discount of 45 percent. so I want the 45 percent to show in cell 4r on sheet one, but if the dropdown box shows customer a & V it should show 35 percent because that is what it is on sheet two in cell 3c.

sheet two has 158 customers and each get a difference discount for that product

View 3 Replies View Related

Excel 2010 :: Converting Hijri Date To Gregorian Date From Another Cell

Apr 19, 2014

I have a spreadsheet where we will be inserting the Arabic Hijri date taken from a Customs document (bayan). On my spreadsheet I'd like to have a column immediately next to the Hijri date that would convert the Hijri date to the Gregorian date so both dates will be visible for our Arabic and Western personnel who will be using the spreadsheet.

I am using Microsoft Excel 2010.

View 3 Replies View Related

Excel 2010 :: Auto-populating Calendar Based On Conditions From Data Table On Another Sheet?

Apr 26, 2014

I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.

On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.

On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".

View 2 Replies View Related

Excel 2010 :: Delete Row If Cell Contains Date Or Any Value

Jan 6, 2014

I came across this excellent code here on this site but it does the opposite of what I need. This code deletes empty or blank rows if the cell in column I is blank. I actually need to delete the row if the cell in column I has a date or any data. Using Excel 2010.

Sub deleteBlankRows()
On Error Resume Next
Columns("I").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

View 5 Replies View Related

Excel 2010 :: Change Colour Of Cell Based On Date In Other Cell?

Jan 10, 2013

I am trying to create a sheet in XL 2010.

In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.

Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.

I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A

I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.

For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.

A1
B1
C1
D1
E1

Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due

January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3

Ive also attached the worksheet

View 1 Replies View Related

Excel 2010 :: Change Cell Colour Depending On Date In Another Cell

Jan 9, 2013

I am trying to create a sheet in XL 2010.

In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.

Column B is when the weekly inspection is due.

Column C is when bi-weekly inspection is due.

Column D is when monthly inspection is due.

Column E is when 6 monthly inspection is due.

I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A

I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.

For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.

A1
B1
C1
D1
E1

Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due

January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3

View 3 Replies View Related

Excel 2010 :: Function For Last Modified Date Of Cell?

Feb 14, 2012

What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.

E.g.
A1 = "AA"
A2 = "=LastModifiedDateOf(A1)" -> "10/03/2011 10:30:32"

-- Make an update:

A1 = "BB"
A2 = "=LastModifiedDateOf(A1)" -> "14/02/2012 12:15:11"

I'm not quite a super user in Excel, but this function would be very very useful for me.

ps. I'm using Office 2010

View 4 Replies View Related

Excel 2010 :: Pass Date From Datepicker In Userform To A Cell In Sheet

Jul 30, 2012

I am using Excel 2010. I have a user form with a date picker on it. I simply want to transfer the date chosen to cell A3 on a sheet. I have two problems. When I click the date picker, it doesn't show all the days of the month. When I do pick a date, it isn't transferred to the sheet. This is the code I am using in the user form:

VB:
Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
meetdate = UserForm2.DTPicker1.Value
Sheets("MeetData").Range("A3").Value = meetdate
End Sub

View 5 Replies View Related

Excel 2010 :: Data Validation Won't Accept Start And End Date From Cell Values?

Mar 16, 2014

Data validation in an Excel 2010 workbook.

I want the date input to be restricted a start date and an End date specified in two cells on the sheet. However, when I set up the Data Validation, ANY date will be accepted.

As an example cell Z1 contains the date 1/3/2014 and Cell Z2 contains the date 31/3/2014, so in the Data Validation box, the Start Date is referenced to Z1 and the End Date is referenced to cell Z2.

But I can enter 1/9/2020 and the Data Validation happily accepts that date.

View 12 Replies View Related

Populating A Cell With A Calendar Month Based On The Previous Date In Another Cell.

Jan 9, 2010

Trying to word this right. I have one cell with a date of 01/01/2010. I have other cells that I want to be equal to this cell plus 1 or more months.

For example A1=01/01/2010

I want A2 to = 02/01/2010 based on one calendar month entered into A1. So if A1 changes 03/01/2010, A2 will = 04/01/2010.

View 3 Replies View Related

Excel 2010 :: Userform Date Picker Textbox Will Not Select Current Date

Feb 10, 2012

I have userform with date pickers and have text boxes overlaid on these, when I select todays date from the date picker it does not display the current date in the text box (I have 8 date pickers on the userform). If I select another date then reselect the current date it works. It has occasionally worked but why.

Below is the code for populating the text box from the Date Picker.

Private Sub DTPicker1_Change()
TextBox1.Value = DTPicker1.Value
End Sub

The initialize userform code uses the following to format and set the textbox

Code:

TextBox1.Value = Format(Date, "dd-mmm-yy")
TextBox1.Value = ""

Windows 7 with Excel 2010

View 7 Replies View Related

Excel 2010 :: Populate A Daily Calendar Using The Dates Between Date Of Arrival And Date Of Departure

May 30, 2013

I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.

View 2 Replies View Related

Excel - Check For Duplicate Data When Populating A Cell?

Jan 27, 2014

I have a big database of customers, each one has a unique reference number. They are spread accross a bunch of different tabs (21 in total to be exact)

One issue I have had is staff entering a customer whos already in the database, causing a duplicate entry. I dont need excel to tell us where the entry is, just to give some kind of indicator it already exists. I thought I may be able to use data validation/conditionality to turn the cell fill Red when it already exists in the data base.

The reason I think that method would be best, is that the sheets that data is entered on are seperate that the master sheet then pulls the data through from. Its the master sheet that would need to indicate a duplicate has been entered, as thats the only sheet where the entire database can be viewed.

View 2 Replies View Related

Excel 2010 :: How To Put Date Picker

Oct 15, 2010

I need to put date picker in excel 2010, the earlier doen't work.

View 5 Replies View Related

Excel 2010 :: How To Add A Date Picker In Spreadsheet

Oct 14, 2013

I just want to add it to my excel 2010. How can I add a date picker in my spreadsheet, when the cell contains a date format????

View 14 Replies View Related

Excel 2010 :: Date Sometimes Changed Into Text

Mar 11, 2014

When I combine 3 reports (which we get out of a system) into 1 big file, the date format remains the same (mm/dd/yyyy and right alligned). Same happens for most of my colleagues.

When 1 particular colleague goes and combine these reports, I've noticed that some of the dates are showing as text? (dd/mm/yyyy and left alligned).

View 3 Replies View Related

Excel 2010 :: Cut Row Based On Date In Column

Dec 21, 2011

I have a fairly large database in excel 2010 that in being updated everyday by a employee. In column A there are a set of dates all in the same format 9/1/2011 what i would like to happen is that if there are any dates in column A then it cut out the row and paste it into Sheet1 (the data is stored in sheet2).

View 6 Replies View Related

Excel 2010 :: Checking Date For Two Columns?

Apr 15, 2013

I have two excel 2010 files that I want to run this on. Each one has about 10+ worksheets in them. I am looking to have a vba script that will look at columns D and E starting at row 4 and check if they are expiring in the next month or have already expired (before today's date). Then it would return a text file that will say which worksheet it is on, the row and column, and what date is in that cell.

View 9 Replies View Related

Excel 2010 :: Converting Text To Date?

Aug 20, 2013

i have the text as string data in column A and it is just a text not date format. i want to convert this using formula text function to get result shown in column B and C. is this doable?

Excel 2010ABC1DateRevised DateRevised Date 2Fri 4 Feb 2011Friday, February 04, 20112/4/20113Fri 5 Oct 20074Fri 28 Apr 20065Fri 30 Sep 20056Fri 23 May 20087Fri 3 Feb 20068Fri 30 Sep 20119Fri 11 May 200710Tue 1 Jan 201311Fri 13 Aug 201012Tue 25 Jun 201313Fri 8 Jun 201214Fri 25 Mar 201115Fri 12 Feb 201016Fri 5 Sep 200817Fri 6 Nov 200918Fri 6 Mar 200919Fri 21 Aug 200920Fri 6 Jan 201221Fri 5 Jan 200722Fri 15 Jan 201023Fri 12 Jun 200924Fri 7 Jul 200625Fri 21 Oct 201126Fri 31 Oct 2008Sheet4

View 5 Replies View Related

Excel 2010 :: Add 12 Months To Date And Then Subtract Today

Apr 25, 2014

I'm in Excel 2010, and the cell with the date I want to work from is G22e?

EDIT: I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days. (I also meant H22 not G22)

Here's the latest thing I tried (that doesn't work):

=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))

EDIT #2: I guess I should probably note that the date in H22 is the result of another formula

=EDATE(G22,12)

View 7 Replies View Related

Excel 2010 :: Plotting Values V1 / V2 / V3 And V4 According To Given Date And Condition

Mar 25, 2014

Excel Version : MS Excel 2010
Attachment filename : <Forum to plot the values.xls>

My requirement is i want to plot the values V1, V2, V3 and V4 from the given condition (A1 / A2 / A3 / A4) and the given date.

If the given date is not available then the formula should take the older date than it not next date. For example if the given date is 10-Mar-14 and the date is not available for the given condition then it should take the older / earlier dates like 09-Mar-14 or 08-Mar-14 like and it should not take 11-Mar-14, 12-Mar-14.

The values should be plotted in the yellow cells.

View 1 Replies View Related

Excel 2010 :: Average Days Between Two Date Ranges?

Jul 5, 2012

Using Excel 2010.

I am trying to come up with a formula that will return a total average from two columns of dates with criteria. The range will need to cover an entire column as my data is continuously growing and the criteria would have to limit the start date to each month. I have tried

=AVERAGEIFS(DAYS360(A:A,B:B),A:A,">5/1/2012",A:A,"5/1/2012",A:A,"

View 2 Replies View Related

Excel 2010 :: Identify Range Of Dates For Given Date?

Jul 26, 2012

I've gotten the desired result in Column B, but this will not work going forward as we add to the table in columns E:G .

I'm looking to search between columns E:F, Identify the date-range where my dates in column A belong, and pull the corresponding rate from column G into column B.

Excel 2010
A
B
C
D

[Code]...

View 8 Replies View Related

EXCEL 2010 :: Conditional Formatting With Date Formula?

Oct 11, 2012

USING EXCEL 2010

I'm looking to create a conditional format which highlights a date RED if it is within 90 days of today's date (the date the file is opened). What would the formula for that look like?

View 2 Replies View Related

Excel 2010 :: Averageifs Formula For A Specified Date Range

Nov 27, 2012

Using Office 2010, I am trying to do a averageifs formula for a specified date range. I can get it working by specifying the date range in the formula itself, however when "pointing" the formula to a specific cell with a date in it, the formula gives me a div/0 error.

Formula that works is:
=AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"08/29/2012")

What I need is a version of: (currently not working)
=AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"H1")

The date range will constantly change as I want it to show me the past 30 days only.

View 1 Replies View Related

Excel 2010 :: VBA - Using Date Via Defined Name In Match Function

Feb 20, 2013

I'm using Excel 2010. I have three columns. Column A contains the date, Column B contains the time, and Column C contains the date and time. Column C is the result of a formula, which adds Columns A and B (ie. =A2+B2). So I have the following...

Date
Time
Date and Time

1/15/13
9:00:00 AM
1/15/13 9:00:00 AM

1/15/13
9:00:05 AM
1/15/13 9:00:05 AM

[Code] ....

I'm using a defined name to store a date and time, which I'm going to use as a lookup value in a Match function. I've defined MyVal as follows...

1/15/13 9:00:20 AM

Excel automatically converts this to the following decimal number...

=41289.3752314815

When I first run either of the following two macros, they correctly return 5...

Code:
Sub test1()
Dim x As Double
x = [MyVal]

Debug.Print Application.Match(x, Range("C2:C10"), 0)
End Sub

Sub test2()
Dim x As Date

x = [MyVal]
Debug.Print Application.Match(CDbl(x), Range("C2:C10"), 0)
End Sub

However, when I save and close the workbook, and then re-open it, these macros both return an error. Is this some sort of bug in Excel?

View 9 Replies View Related

Excel 2010 :: If Statements For Date Ranges (Nested)

May 18, 2013

I have got a table with data from 2005 to date, (for example) For the purpose of what I am doing I need a column which shows date ranges between September 2005 - August 2006 to show as 2005/2006, then September 2006 - August 2007 to show as 2006/2007. I have done some research and seen that, Potentially, a nested 'IF' can be used but it can only be used 7 times which would cause a problem going forward....

Is this the only way or is there a better way (without using VB)?

I am using Excel 2010

View 7 Replies View Related

Excel 2010 :: Formula For Duplicates Within Date Range

May 31, 2013

How to set up a formula on Excel 2010 using Conditional Formatting.

I want to identify duplicate account #'s within 15 days of the work date. Here is sample of the report.

Account Number
Facility
FC
Payor
Current Balance
Aging Level
Team Member
Work Date

87890
HHSS
1
BCBS OF TX (PPO)
$9,616.94
121+
Steven Johnson
5/20/2013

[Code] .........

View 4 Replies View Related

Excel 2010 :: Autofill Date Into Headers Of Column

Feb 19, 2014

I have been struggling to find where my code is throwing up an error 1004 on the last line of the below code. I have a number of tables which will update automatically from Pivot tables on another sheet.

So the first part of the code is adding in the new column and then I want to autofill the date into the headers of the column which I thought the below would do, but I just don't understand why I keep getting the error. My data is dynamic as it will grow month by month which is why I am using R1C1 referencing.

Sheets("PNN Table").Cells(9, 16384).Select
ActiveCell.End(xlToLeft).Select
ActiveCell.Offset(0, -2).Select
Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, 1))

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved