it has various headings - but the ones I am stuck on are below.
BADGENAMEDATE Issued
He has set up a basic vlookup so that typing in his badge number also will type out his name. However, he also wants the current date to come up every time he does this.
As the names and badge number don't change and have no difference from row to row, how can I get it to date stamp it with a different date each time?
I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.
The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column
These dates also have formula by which there are derived
-------------------------Current Ageing-------------------------
Date Range
Bucket
Amount
Percent
Start Date
End Date[code].....
I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.
In this spreadsheet I am working on for calculating vacation time based on accrual rate. how to add pay period dates automatically based upon the first on entered. I have done this before in a personal budget spread sheet and this is what I have in that. First cell I enter the date Second and following cells I have the formula "=IF(DAY(E$8+7)>7,E$8+7,"")" Everything works perfectly. No matter what I put in the first cell the following cells will adjust and display dates 7 days later from the previous cell.
I am trying to do the same thing except pay period dates are 14 days apart so I put the same formula except change the "7's" to "14's). The problem I am having is that ONLY the first cell displays the correct date, the second displays a blank cell and the remaining displays "#VALUE!". What the heck is going on? I cannot figure out why this formula works in one circumstance but not the other.
I am not sure what I am doing wrong, but what I am trying to do basically is what ever I enter in the first cell, I want all remaining cells in the date column to enter the date of 14 days later. Example, I enter in the first cell 3/27/09, in the next cell I want 4/10/09 and in the next cell I want 4/24/09 to be entered and this all the way down.
The code is supposed to project review dates 1 month, 2 months and 3 months after the start date, but the first "cell offset" line on is projecting the next day instead of next month, its driving me nuts!!, the others are working fine
Code:
Sub reviews()
Dim cell As Range
For Each cell In Range("L1:L200")
If IsDate(cell) Then
cell.Offset(, 10).Value = DateAdd("m", 1, cell.Value)
cell.Offset(, 11).Value = DateAdd("m", 2, cell.Value)
cell.Offset(, 12).Value = DateAdd("m", 3, cell.Value)
cell.Resize(, 10).AutoFill cell.Resize(, 11)
End If
Next cell
End Sub
I have a spread sheet that shows the dates items were shipped. When trying to work out the value of parts shipped in a month using SUMIF I get the value zero, I believe this to be because (obviously), there are multiple dates of ships during the month, I therefore tried ignoring the day of ship in the SUMIF but this did not work, the date format is DD/MM/YYYY.
View 9 Replies View Related1) To be able to get the current date in the format of 3/10/2008
2) To be able to take dates from 2 cells and get an integer value for the number of days between the two dates
I'm trying to limit the information people can put into my format and therefore to ensure they put in a the correct date etc i'm wondering if it is possible to have a calendar pop up to click on the date for a particular cell?
View 9 Replies View RelatedI'm having great difficulty adding hours to dates in Excel. In cell A1 I have a number which is calculated from a formula, it will normally return whole number values between 1 and 100. In cell A2 I have a start date and time i.e. 14/2/07 06:00:00 in cell A3 I want to add the number in A1 as hours to the date in cell A2. I am using the formula in A3 as =A2+TIME(A1,0,0) it seems to work okay for numbers that don't take the date onto the next day but if I try to add 24 hrs or 48 hrs for example the date still stays the same.
View 7 Replies View Relatedhow i can automatically add a week to a start date i have in a rotas for the whole year?.
View 3 Replies View RelatedThe way the code works right now is that I have it run as a few loops. it will first loop through the first worksheet in the array and delete what I need and then when that loop finishes it will run two macros that I have added in, then it will loop to the next worksheet in the array. right now it is only 2 sheets but eventually it will be many more. just working out the code for now. What I would like to do is in the second loop. my second macro in the code formats each page and, what I would like to do is add a date column to my code. I need to start at 1/05/2014 and then for the next iteration in the loop add 7 days.
[Code] ......
There are 5 columns.
Engineer name, attended site(date), report received(date), commercial/heating and bonus(gbp)
Engineers will only get a bonus if they send the reports in 1 day after attended site date for commercial, and 2 days after for heating. If the report is received past this window, they do not get a bonus!
Weekends do not count as a day!
This should be on one row per engineer. I then want to see how much "John Doe" is owed in any given month...
a sample of my sheet first....
View 9 Replies View RelatedI am having a 2000 records of date and time in excel (see the below example). My query is I want to add 6 hrs to each Cell and accordingly the Date should get changed for e.g see the B Column after adding 6 hrs the data should look like this. I want an excel formula (don't want VBA Code) ...
View 9 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.
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).
How can i leave Cell D3 etc blank untill i have inserted the end date it will look tidier when i do my full spread sheet and also is there a calender on a toolbar i could put on my spread sheet for easy inputing of dates.
View 5 Replies View RelatedI have a column with dates and times, and an associated column with data for all the dates and times. I want to add every hour of every day to the date/time column, but want the data associated with the times to stay in the row next to the correct time. How would I do this? In the attached file, i want to make coulmns A and B look like Coulnds E and F. I have hundreds (if not thousands) of rows, so i need a way to do this easily. Also, the times are not all evenly spaced or exactly on the hour. I dont need the times to be exactly on the hour or spaced out evenly, but i would like to have at least one time from every hour.
DBdate time alter.xlsx
Problem,
A B C D E F (B+C-D-E)
Date Opening Stock Purchase Sales Consumption Closing Stock
17/7/2012 10000 5000 1000 2000 12000
17/7/2012 12000 2000 2000 1000 11000
18/7/2012 11000 0 1000 5000 5000
18/7/2012 5000 10000 3000 0 12000
Note : I want the above result as shown below datewise but no duplication of date but summing up of column C,D and E which is in above.Pls note that this is accounting question and also plz note that Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).
Results :
A B C D E F (B+C-D-E)
Date Opening Stock Purchase Sales Consumption Closing Stock
17/7/2012 10000 7000 3000 3000 11000
18/7/2012 11000 10000 4000 5000 12000
(B=F)
if I could get the formula in excel ,since this is the sample data as I do have a numerous & huge data like shown above datewise where I need to add C ,D and E but Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).
I need to convert the following string into Excel dates:
Jan 1, 2009
Jan 12, 2009
Jan 21, 2009
Right clicking it and changing it to date format will not work.
I have tried extracting individual days/months/years out by using Left/MID and Right functions but as you can see, problem lies when there days with single digit or double digits.
I wanted to determine the number of days between two dates. Specifically, if the initial date is in one month, and the second is in a different month and an output would result a number of days in each month until the final date. BUT I have a large amount of data to do this for in a list view, way to put a formula in excel and just drag down the entire list to get the required information. see below for an example.
The result I'm looking for is the separate the months and only show the relevant months between the two dates in one cell or the adjacent. Something similar to the table outlined below.
Input Data
Result
Start Date
[Code]....
=COUNTIFS('Gone Out'!L:L,"=27/05/2014",'Gone Out'!A:A,"=John Smith")
Where I have the date why will this not work?
=COUNTIFS('Gone Out'!M:M,"=E12",'Gone Out'!B:B,"=John Smith")
I have 27/05/2014 that in E12 but it keeps giving 0 instead of the actual number
Also, How would I make it so that instead of adding 1 to the value, i could add John Smith's Age?
I have a userform that I enter information into fields and I would like to created a command button that will insert the information into the workbook ....
View 14 Replies View RelatedHow to add an appointment to a shared outlook calendar from Excel. I will be entering shipments in an Excel page and would love to just click a button and have an appointment made. I am very much a novice on how programs communicate with one another. The only information that will need to go on the appointment is the subject, location, and time. We set all our appointments at 15 minutes duration. The code I have so far is this:
[Code] ....
The problem is it only adds it to my default calendar. I need to be able to add it directly to the shared calendar in Outlook.
I would like to add a search box to worksheet 1 in my workbook of 162 sheets. I need the search box to search sheet 23 cell range B11:B342. These cells contain names of employees.
I would like the option of entering a name into the search box and either hitting enter or clicking a "go" button to search. If the search is successful, ideally a function would automatically redirect the user to the located text and if the text cannot be found, a message would appear indicating as such.
When it comes to VBA, I am a very basic user, so I would need the code written out for me and I need to know exactly where to put it and how to get the search box to appear.
I am wondering if it is possible to use a formula or IF statement within a macro to add a single row at a time if a cell shows as False? For example, if the value in column AD is False, I want it to add a single row under that row that has False. However, if the value in column AD is True, I don't want it to add a row and skip to the next record. I need it for approximately 500 records.
View 2 Replies View RelatedI have a date stored in a variable and I need to add month(s) to that date. For example if I have 10/1/2013 in a variable if I add 1 to that month the result should be 11/1/2013. I am using the following formula but the problem is in the result date format, which shows as 1/31/1990, if I add 2 to the original date, the result is 2/29/1900 which ideally should have been 12/1/2013.
My formula is :
ActiveCell.Value = "=date(year(" & last_date & "),month(" & last_date & ")+" & i & ",min(day(" & last_date & "),day(date(year(" & last_date & "),month(" & last_date & ")+" & i & "+1,0))))"
Any other formula to add months to a given date?
I am using the code below (Excel 2007) to delete and add back timesheets based on a list of names from the "Names" tab. In addition, I am making a "Table of Contents" with hyperlinks on a separate worksheet that will allow employees quick access to their timesheet without having to look at each tab. The code below works well if I want to delete and add back all the timesheet at one time but if we get a new employee in the middle of a pay period I have to use a single timesheet until the end of the pay period.
What I would like to know is, can the code below be modified so that when the code is run it reviews the list of names and only adds a new timesheet for that employee without deleting and adding back all the timesheets?
I was viewing this thread here: [URL] ...
How to add checkboxes and radio buttons.
The code below resides in my personal.XLS Module. When I step through the code everything works till it reaches the point in red then I get the pop up that says excel needs to close and asks to send or not to send.
I noticed that if I choose recover my work and restart excel and I look at the code in THISWORKBOOK of the recovered workbook that the code I wanted placed there is there.
Can anyone explain why this is happening? I have the macro's security set to low and a check mark in the trust access to vb projects.
Any Idea's how to fix this?
I have been trying to write a macro to add a page break in Excel 2007, but I have had no success.
A friend recomended:
Columns("CC:CC").Select
ActiveWindow.SelectedSheets.Vpagebreaks.add Before:= ActiveCell
This has not had any affect when I have printed.