Can I use dates as argument in Boolean arithmetic? I have a list of name with their date of birth and I would like to tell who is between 18 and 25. It's easy enough with number but with dates? Excel 2007
View 9 Replies
I am attempting to pass a boolean variable between two macros. The SUBMIT macro calls the STATUS macro to do some verifications and the STATUS macro should return the boolean variable RDY back to the SUBMIT macro, but does not.
I am using Excel 2007. Both macros are in the same Module. The STATUS macro is called from several places in the code.
Public Rdy As Boolean
Sub Submit()
[code]
Call Status
If Rdy=True then msgbox("You have completed your form") else msgbox ("Not ready")
[code]
end sub
[Code]....
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).
I have a column A with following values below:
Column A
"VL50s"
"M50s"
"H50s"
"VL50s"
"H50s"
I would like to extract the numbers and run the following arithmetic function below into column B.
key:
x is a number
VLx --> (x) + 1
Mx -->(x) + 2
Hx --> (x) + 3
the output should look like the following using the key above:
column B
51
52
53
51
53
Here is a very simple workbook/sheet with some simple date arithmetic.
I keep getting the warning that there's a circular reference in C2.
Either I need to suppress the warning so vba loops can run or PREFERABLY I'd get rid of the "circular reference".
Run with Excel 2013.
I have a Billing Data where i want to Calculate TAT between two dates in Excel 2007.. I received the Invoice from Vendors on specfic date but due to discrepances, i rejected the invoice, after few date i received a rectified Invoice from Vendor... so how can i calculate the TAT from 1st Receipt to Rejection..
Column A Column B Column C Column D Column E Column F
(Receipt Date) (Rejected Date) (Re-Receipt Date) (Rejected Date) (Final Receipt Date) (Formula for Finding
Rejection TAT)
E.g : In Column F, TAT to be calculated from 1st Receipt Date to Rejection Date, but if i received the rectified bill & after inputing the Re Receipt Date in Column C, then the Cell in Column F will remain blank, but again if it is rejected due to some error, & after mention the 2nd Rejection Date in Column D, then the TAT to be calculated from 1st receipt to 2nd Rejection (Column D), but if i received the Final Rectifed Invoice & after mentioning the Receipt Date in Column E, then the Cell in Column F will remain blank.. but pls note, if the invoice is not rejected the, the Cell in Column F will remain blank..
I have tried IF Forumula but in vain.. i can only nest 1st receipt to 1st rejection i.e : =IF(AB6= "", "",(TODAY()-AB6))
In functions it is easy to simply say =if(a1>b1, do X, don't do X)
But how do I do this in VBA? Excel 2007
I am using Excel 2007 and am having some trouble formatting a column for dates. I am setting up a template spreadsheet for the company I work for. The template spreadsheet has a sheet for entering the raw data and a sheet for summarizing it. I need new dates that are to be entered to be formatted correctly. I have set up a sample spreadsheet and inputted some sample data which looks like the following:
Date
06.11.13
07.11.13
08.11.13
05.10.13
06.09.13
Now if I format these cells as English(UK) dates with the "dd.mm.yy" format the data>sort cannot sort these dates from oldest to newest. I can correctly sort them by using "text to columns" however if i do this then it only format's the cells that currently have a date entered into them and not any new date entries.
So in summary: Format cells into a date format does not allow dates to be sorted using the data>sort tool. (I assume that as they cannot be sorted ,a formula that searches for cells with dates before a certain date will not work either) Text to columns does not allow new entries to be formatted in the same way as the cells that had data in them when the text to columns tool was used.
Is there a way to format blank cells so that they will recognize the data inputted as a date?
I have a stripped down data source for debugging purposes. I only have 4 rows of data for test purposes. The dates are formatted as dates.
When I create the pivot table the dates become my column values. When I select the first date in the pivot table the Group By Field menu option is grayed out. I tried setting a tabular format but didn't work.
I am setting up a Cattle management system in excel 2007. In the column F are the date of births for each individual animal. Example: F2 13/03/2013 F3 23/05/2013. I would then like to highlight the cells with the dates in to find all the cattle that are under the age of 16 months from the current date.
View 9 Replies View RelatedWhat I have done is entered code to auto generate the date in column O whenever data is entered or altered in column A. Here is that code:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then _
Target.Offset(0, 14).Value = Now
End If
End Sub
What I am trying to do now, is create a column that will take the information from O and do a sort of COUNTIF function that will count how many items of data were entered on a certain date by the day. For a clearer example, I want it to tell me how many items were entered/altered on 7/23. But I also want it to continuously calculate it for each date after that. Preferably automatically, but if a macro is needed I can create an update button.
Once it can achieve that I would like to create a dynamic graph that will automatically (or via macro button) update to show the last 5 days. It should display the date and how many items were entered that day.
I am using Office 2007.
I have a column in my data set that consists of dates and times in this format: 2014/08/02 01:46:49 PM. I am trying to convert these dates and times to numbers so that I can actually use these values for calculations and regression analysis. When I click on one of the cells, I get a number that is revealed along with decimals. For example, the cell with 2014/08/02 01:46:49 PM had a stored value of 41853.574. I tried to highlight the entire column of dates + times and click on the format cells button. I selected the number category under number but that did not work. I also tried to use the =DateValue(Cell #) function but that did not allow for distinguishing between the same date but different times.
What are my options for converting these dates and times to numbers that I can work with? Is there a way to get the entire column of dates + times to show each cell's numeric stored value?
We are using Excel and Outlook 2007 and 2010 in our office and this program needs to work with both versions of each, Excel and Outlook. I need to have a csv file exported from outlook to identify locations of meetings and when they occur. I can do this manually but it could be used at multiple times by different people
View 4 Replies View RelatedI am working with the Ganntt chart and horizontal bar charts but can't seem to figure out a way to force the X-Axis to behave properly.
In Excel 2007, I am trying to get the X-Axis to show major units of Months. However, in my chart options I can only change the Y-Axis to be Date/Monthly.
Changing my data layout (from the below) to be a vertical format produces the same problem, just in the other direction.
My data looks as follows:
System
Blocker
Production
Migrataion
Retired
Sys1
1/1/2012
780
Sys2
1/1/2012
400
60
90
Sys3
2/1/2013
30
5
1
The Blocker column is formatted as "No Fill" in order to cover a portion of the time-scale.
My X-Axis displays as random dates throughout the period and all of my data displays properly in terms of the scale on the grid.
What is NOT working is that the dates shown on the X-Axis really need to be based on a Monthly scale instead of the randomly selected dates Excel is using.
20120823.xlsx
I can't seem to find the right combination of options to make that happen or force a scale on the X-Axis.
Excel 2007 Q: I have a combo box (ActivX) that I want to be able to choose a a date from a range of dates in column A on sheet 'Working Copy'. I want a cell to update with the number/position of the date when the date is chosen i.e. the first date in the range yields 1 the second date yields 2 and so on. I also want the date to be displayed in dd-mmm-yyyy format. I am able to get the cell to give the correct position of the date but the date reverts to five digit number in combo box. I can change the code to return the correct date in the combo box when the date is chosen but then the cell just returns 0.
How can I make it so that the combo box returns the correct date and the cell updates with the position instead of jsut one or the other?
Here is the code I am using
Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Text, "dd-mmm-yyyy")
End Sub
or
Private Sub ComboBox1_Change()
Sheets("Working Copy").Range("G1").Value = ComboBox1.ListIndex + 1
End Sub
I am trying to figure-out how to set up conditional formatting of dates in a column (e.g. Date Submitted) and have these dates, and/or cell, change color as time progresses. I have Excel 2007.
For instance I have an application submitted on 11/20/2011 in a cell. I would like to have the date change color (Yellow) after 30 days have passed.
Then do the same except change to Red after 60 days have passed the date.
I searched the forums and did not find this particular problem. I of course may have missed it too...
My guess was to try "$A$1
Excel 2007 I have a spreadsheet of file boxes the keeps a log of boxes, contents, locations and shred dates. Currently figuring out when and which boxes can be shredded is a manual hunt, find, and then deal with it. I can use CF and Sorting but I have been looking at some search type functions to return a list meeting the requirements and trying to step my way towards that.
1st attempt. Was find out how many boxes meet the shred (before today) that have not already been detroyed. Column K is the Shred Date, D1 is Todays date, Column G is Status. Got this working good.
[QUOTE]]=SUMPRODUCT(--(Log!K2:K2136(lessthan)STATS!D1)*SIGN(LEN(Log!K2:K2136)))-(COUNTIF(Log!G2:G2136,"Destroyed"))[/QUOTE
My failed attempt came at trying to find the earliest shred date excluding those that have been destroyed. This is not really neeed but I was hoping it would get me closer to creating the list of boxes that meet shred that have not already been destroyed.
Note: Column A is the Box No. and the spreadsheet contains blanks as we continue to add file boxes to storage.
Below array formula find the highest box number used by each department.
Code:
=A4&"-"&TEXT(MAX(IF(Log!$A$2:$A$2136"",(LEFT(Log!$A$2:$A$2136,LEN(A4))=A4)*RIGHT(Log!$A$2:$A$2136,3))),"000")
I would like to create a list of boxes including Column A "Box no.", Column H "Location", Column K "Shred Date" for any box whose shred date is before today that has not aleady been labeled "Destroyed" in Column G.
When I attempt to send a spreadsheet using a German version of Excel, the following formula does NOT work properly.
=ZELLE("adresse",C14)
The function gets translated into =CELL("adresse", C14), and what I need is, obviously, = CELL("address",C14). The argument does not get translated.
In an Excel Pro Plus 2010 workbook on a Windows 7 PC, trying to use the "After" argument in a VBA .Find function and it's not working as expected.
I've got the following data in column D:
Row1: Title
Row2: Row2Data
Row3: Row3Data
And using this simple procedure:
Sub FindTest()
If Range("D:D").Find("Title", After:=Range("D1")) Is Nothing Then
Debug.Print "Not Found"
Else
Debug.Print Range("D:D").Find("Title", After:=Range("D1")).Row
End If
End Sub
My expecation is that "Not Found"will be returned, but instead I'm getting the row # (1).
If I change the value in D1 the procudure returns "Not Found", as expected.
If I change the find argument to look for "Row2Data" it returns 2.
I also tried changing the "After" argument to "D2" ... same result.
Finally, I tried using this format: "Range("D:D").Find("Title", [D2])" and got the same result.
I have a cell (G11) whose format is [h]:mm to store hours worked in a week.
I need to use that in a VBA function. If I query G11.value I get a non-integer number (I DO know that Excel stores time internall like that). How can I get in a VBA procedure exactly what is see in the cell..e.g if someone worked 35:15 hours, I want to be able to get 35:15 in the procedure. I need to strip it from there to work out payment, eg (hourly rate * 35) + (hourly rate * (15/60)/100).
I have tried using format but it does not like the "[h].mm" argument.
I'm trying to put a simple spreadsheet together that will help my son practice
his arithmetic for primary school.
What I have put together is something which will allow him to change say his multiplication tables fairly easily by changing one number.
Conditional formatting shows green if correct and red if wrong - all very easy.
However I think it would be good to show the selected problem in a top down layout
so he can visually see what he is trying to do rather than read across the page.
4
x 2
==
8
rather than 4 x 2 = 8
What I would like to do is change this displayed problem when selects the answer cell
he is working on.
I will attach what I have done so far with a display example on the multiplication tab.
Is it possible to create a formula that would give the sum of cells that are in arithmetic progression in excel?
Example:
Let's first choose 4 cells that are in arithmetic progression, B14 , B20 , B26 and B32 for instance(the common difference here is 6). So what I want to do is: I want to type a formula in another cell, lets suppose C5, that will automatically give me the sum of the values of B14,B20,B26 and B32. I am aware that I can just type on C5 =B14+B20+B26+B32 but and if I wanted the sum of 90 cells? Wouldn't it be too much work to type all the cells? Does Anyone know a formula for it?
is it possible, in a function, to do something like this?
Lets say I'm in cell N7, and here's its formula:
IF (N6=$A$13,(A+1)$13,0)
-if the cell before it (N6) is equal to the value of $A$13, then I want N7 to equal the value of B13. If N6 is not equal to $A$13, then place a 0 in cell N7.
basically, I would like to tell my function to move to the next column if it meets a condition in an "IF" statement. Such as, go from column A to Column B given a certain condition. Otherwise, stay in column A until that condition is met.
I have a timesheet in excel which details the hours worked per person. It is worked out by have time started in one cell, and time finished in the next cell. (24 hour clock).
The timesheet is for a night club, so people start late, and finish early. Therfore, in the total column I have the following formula...
=IF(D5="",0,IF(D5
I need to switch between less than or greater than in my formula based on user selected drop down that gives the user two options "" My formula has "
View 9 Replies View RelatedTime arithmetic, I have two cells representing a time range.
The first one (say: X1) is formatted using the custom format [h]:mm and contains a certain number of hours and minutes. It gets its value by summing up other cells in the same format. A typical entry could be 98:35 to represent a duration 98 hours and 35 minutes.
The second cell (say: X2) is formatted as a number with 4 decimal places after the comma, and similarily gets its value by summing up other cells in the same format. It also represents a time duration as a number of hours. A typical entry could be 202.7500 to represent a duration of 202 hours and 45 minutes (because 0.75 of an hour is 45 minutes).
I would like to calculate the hour difference between these cells, and display it as hours and minutes. In the example given, the result should be negative, i.e. -104:10.
My first approach was to use the formula X1-X2 and format the result as [h]:mm, but this gives me a #VALUE! error.
I'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 have a timecard spreadsheet which im working on, each employee is represented by a single worksheet in the workbook, and the worksheet is named after the employees name. New employees are added by filling in a form which dynamically creates the timecard based on a template. Im trying to impliment a 'Fire Employee' button which sits on each employees sheet and can be clicked to set an employees working status to false by changing a boolean variable called 'fired' to true.
Ive been looking around the forum but I cant figure out if i should be using public variables or where exactly i should be placing them, so that each employees 'fired' status is set to false when the worksheet is first created, but can be set to true when the 'Fire Employee' button is clicked, this must only set true for that specific employee, and not all employees. If your wondering why i need this, the employees work status will be used on other forms to toggle views etc so thats why a boolean approach is best (at least i think it is if its not to difficult to do)
I have minimal excel knowledge, but enough to get by. I have 2 years in C++ programming.
Here's what I'm trying to do:
I'm trying to create a workbook to keep track of flags we send out, and receive back. We have a lot going and coming, so I figured the best way to do this would be to break down each region on different sheets. But I need to create a check box that says something like, "Yes/No" or "Here/Gone" or something like that. THEN, I need to take that information, and put it on the front sheet of the workbook so I know how many we have in/out total. Does this make sense? I know how I would program this in C++, but excel is a bit different.
once bTest is declared as a Boolean, it becomes equal to FALSE (I checked this after much head scratching). How does one assign a value to a boolean variable only after you make your test.
Sub foo()
Dim bTest As Boolean
If Range("a20").Value = False Then bTest = False '
