Min & Max (30+ Cells Selection) + Date Conversion
Feb 9, 2010
Excel won't take more than 30 cells when using Min or Max function. I am trying to get min & Max of the following:
=min(M3,O3,Q3,S3,U3,W3,Y3,AA3,AC3,AE3,AG3,AI3,AK3,AM3,AO3,AQ3,AS3,AU3,AW3,AY3,BA3,BC3,BE3,BG3,BI3,BK3,BM3,BO3,BQ3,BS3,BU 3,BW3,BY3,CA3,CC3,CE3,CG3,CI3)
Is there an alternate to this? The selection is every alternate cell starting M3, O3 & so on.
Also, All of the above reference columns have date stored in "2002 Aug 02" format. Is there a quick way of selecting the columns automatically & converting it to DD-MMM-YY?
I tried various methods but nothing works unless I manually enter the date. I am looking for an easy way because I have 100's of sheets to fix with 30+ columns. It will be a big and tedious job if I try to create a separate column beside each and then convert using a formula.
View 9 Replies
ADVERTISEMENT
Mar 25, 2014
How to convert this cell date time to 2 separate cells
A1 = 20140320 093000,
to B1 = 2014/03/20 and C1 = 09:30:00 am
View 2 Replies
View Related
Nov 1, 2008
if it is possible to convert Gregorian date into Islamic date by using a formula or VB code in excel? If yes, what is it?
View 9 Replies
View Related
Apr 3, 2007
I am trying to convert numbers from three cells into a date in one cell. I tried using the =DATE function, but it did not work. In A1, B1, C1, I have 1, 15, 2002. I would like cell D1 to take the info from the preceeding three cells and display "January 15, 2002"
Presently, I have A1...C1 formatted for "General." D1 is formatted for "Date." Yet I get the answer "August 23, 1907" (or 2792 as 'General').
View 3 Replies
View Related
Mar 10, 2014
Is it possible to be able to select a range of dates, and a time interval, and then to automatically fill in cells in a column? to be more specific, I want to be able to select, say 1 Jan 2013 to 31 dec 2013 in one box, then specify a time interval, for example "hourly" and then a column is populated with each interval, i.e. 1 jan 2013 00:00, and then the next one is 1 jan 2013 01:00 etc. I have tired the normal automatic fill tool but it never seems to get it right. It works for the first few days then it all goes funny.
View 3 Replies
View Related
May 20, 2014
I would like to convert date written like this "April 24, 2014" in Excel Date format.
Is it possible to do it with a formula or data import?
View 6 Replies
View Related
Nov 14, 2006
IS THERE A FORMULA TO CONVERT THE 5 DIGIT # INTO ITS CORRESPONDING DATE?
View 9 Replies
View Related
Aug 28, 2009
I use the following codes to convert YYYYMMDD into DD/MM/YYY
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
I use this on quite a regular basis, I would like to convert this to a macro so I can simply select the cell/cells and press the button.
Firstly is this possible and can anyone provide a script?
View 9 Replies
View Related
May 4, 2006
Basically my requirement is to find out the time difference in terms of minutes between two days of time.
For example. the difference between(in minutes) 27/04/2006 10.01 and 29/04/2006 11.02. The reasults i want is in terms of minutes only.
View 7 Replies
View Related
May 7, 2006
I'm trying to convert a number from word to a number in excel. Example: 10-12 input in word to a cell in excel. Unfortunately it treats it as a date an gives me 39002. I want to be able to use both numbers and add them together to get -2 not 39002. How to format the excel spreadsheets in order to complete this calculation.
View 4 Replies
View Related
Mar 19, 2008
I have come across an issue and I am not sure what the best way is to achieve my goal or if it's even possible. We are importing dates that are in IBM DB2 Date format. Example: the date is imported as 1060318(C/YY/MM/DD), which is is actually 2006 March 18th. I would like to convert 1060318 to something that Excel can format into March 18th 2006.
View 9 Replies
View Related
Sep 20, 2013
I am currently trying to make a digital time card for my place of employment. I has an odd set up and odd date ranges. The pay period starts on the 21st of each month and ends on the 20th of the next month.
The time cards have 5 columns for each week starting on Mondays going to Sunday (also weird its not Saturday to Sunday) What I was hoping to accomplish was being able to select the starting month of your choice for pay period and have multiple cells update the week date range.
Date
Date
Date
Date
Date
**Here would be the drop down month select.
Sept 21 - Sept 22
Sept 23 - Sept 29
Sept 30 - Oct 6
Oct 7 - Oct 13
Oct 14 -Oct 20
View 2 Replies
View Related
Feb 6, 2014
I have a text string from a regulatory site for a date and time
01/11/2013 1607
I'm trying to convert this into a date and time stamp but having problems with datevalue
11-Jan-13 16:07:0
View 3 Replies
View Related
Apr 13, 2014
In the attached sheet, one macro is developed which is not working properly. My req is to convert date from dd-mon-yyyy to yyyy/mm/dd format. (23-jan-2010--> 2010/01/23)
If incomplete date is there, then hyphen should place the gap i.e.
For date like APR-2014------> Output should be 2014/04/--
For date like 1998 -----> Output should be 1998/--/--
The below macro works only for row number 11, 15 and 16.
[Code]....
Attached File : date.xlsm‎
View 5 Replies
View Related
Aug 19, 2012
How to create the formula for dates formatted with periods.
Dates are as follows:
01.07.12 - should return as 1-Jul
01.08.12 - should return as 1-Aug
02.08.12 - should return as 2-Aug
View 4 Replies
View Related
Dec 24, 2012
I have a question about conversion of a text format into date format.
2 computers, the first windows vista, the second Windows 8, both Excel 2007.
The purpose is to calculate the difference between 2 dates with hour:minutes:seconds, like for example:
18/12/2012 19:20:00 and 18/12/2012 19:23:03.
So in this example the result should be 00:03:03.
Both dates are formatted with 'personalized' : dd/mm/yyyy hh/mm.
In the first computer everything works like it should, but in the second it is apparently impossible to transform the first of those date h:m:s which is originally in a text format into a date format. I tried all the methods which i found to transform a text date format into a date , like multiplication with 1, Datevalue and others, but nothing seems to work, the result is always "#Value".
View 4 Replies
View Related
May 6, 2006
I need this: 38842.8287152778 to be converted into 05052006_075321PM. This is the date for May 5th 2006 @ 7:53:21 pm in VBA. How do i get there from 38842.8287152778?
View 9 Replies
View Related
Jul 22, 2008
How to assign a variable of type Long to the below code.
1) Dim searchField as LOng
ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200=""" & searchField & """,$C$2:C$200))"
2) ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200= searchField,$C$2:C$200))"
1) code failed because I want the variable searchField to have a data type of LONG.
2) code failed because it seems like excel thought the search criteria is of same searchField.
How to convert date to general format and store it in a variable of data type Long. I.E if I convert 3/15/2008(data type of Date) to data type of General, it should be 39522. What is the VBA code for doing this conversion?
View 9 Replies
View Related
Oct 31, 2008
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.
I would like to have a seperate start date cell and a go live date cell.
The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.
View 9 Replies
View Related
Dec 30, 2009
Is it possible to click on a cell and a monthly calendar pops up for you to select a date to input? The output will be just the DD/MM/YY format.
View 10 Replies
View Related
May 8, 2007
Want to find out if there are ways to call data with a week as condition. I have attached a sheet which is a part of a macro which basically calls data to another location and conslidates it on a weekly basis. I have given a drop down which enables me to change the data on a 7 days basis but this is not effective as it does not correspond with the week begning and end is there any other way in which when I select any of the weeks data gets picked up from Monday to Friday?
View 2 Replies
View Related
May 21, 2007
I'd like to use a calender ifor this. To start, I did the following:
From Control Toolbox - More Controls - I selected:
Miscrosoft MonthView Control 6.0 (SP4)
This placed a calendar on my worksheet.
What I would like to do is the following
1. Calender Display
1a. When a next button is selected in my wizard it moves to a new form. (Got this done)
1b. The Userform has buttons: "Select Start Date", "Next", and "Cancel" (Got this done)
1c. When user selects date button, this triggers the 'Calender' to display.
Currently, ithe calender is still showing, so I first need to hide it.
It seems to be called "MonthView1" and has the following in the formula bar when Control Toolbox is in edit mode:
=EMBED("MSComCtl2.MonthView.2","")
2. Date selection
2a. User selects a date on the calender. (Easy if calender is displayed)
2b. User selects next button in User Form. (Triggers what follows....)
3. Variable assigned from selection
3a. 'Next' hides the calendar.
3b. 'Next' calls a marco that assigns the date selected from calender to a date variable.
sDate = ???.Value where ???=selected value from calender
3c. 'Next' hides current form and moves on to next in wizard sequence (I can do this)
View 9 Replies
View Related
Jan 24, 2007
There is a speadsheet at work with a column which has a range of dates (from 18/05/2003 - date) which information gets drawn from every Friday. The information needed from the column is pretty simple but lengthy to expalin. For example...
[url]
As you can the dates are in no order...and need to stay that way. The information needed every week is shown here...
[url]
I have got the formula for the number of enteries between each date but strugling with the earliest date within the dates. I can do it manually by auto filtering the column and looking for the date closest to the earliest date but this is long.
The dates change on a weekly basis...just to throw a spanner in the works.
View 9 Replies
View Related
Nov 3, 2008
There's a macro I'm trying to figure out for a calendar I'm working on. Here's what it has to do.
1.) Selects the cells for the current month (I assume the user would have to click on the current month itself) plus the two upcoming months. i.e. November, plus December and January.
2.) Sets the selected cells as the area to print, then prints those cells.
If there's anything that might be a challenge, the numbers for the days are all text boxes, if that makes a difference. I've been experimenting with different formulas with no luck.
View 9 Replies
View Related
Jan 21, 2010
I have a Workbook with about 20 Worksheets, for each sheet I have a Selection Change Event that simply performs the Following:
MainSheet1. Range(“Z1”) = Now
1 Cell on every Sheet then is pointed to this cell via =Main!Z1. Problem is that when this is preformed Each Row from the Other In-Active Sheets and sometimes a near-by Button will Appear on my Active Sheet. I cannot select the parts of the sheets that appear and when I scroll down till they are out of view and then scroll back up they are gone until I select another cell and it happens again. I have tried in the Z1 cell to just put =NOW() and Make Calculations Manual and then with the selection change to call MainSheet1.Calculate or Workbook.Calculate but this has the very same effect and the other sheets bleed onto or ghost onto my Active Sheet. So far the only way I’ve been able to get around this is:
If Application. ScreenUpdating Then
Application.ScreenUpdating = False
MainSheet1.Range(“Z1”) = Now
Application.ScreenUpdating = True
Else
MainSheet1.Range(“Z1”) = Now
End If...............
View 7 Replies
View Related
Aug 11, 2014
The worksheet is basically a sales funnel and I want to use a drop down menu selection in ever row of column Z to update the relevant cell, based on the selection, in the same row, column Q to Y, with a date stamp which will then not change as the drop down is progressively changed to suit the relevant stage of the job.
View 2 Replies
View Related
Apr 5, 2014
I am working with DatePicker calendar. I have two userform with date entries. In one userform I will have 2 textbox to enter dates. In the other one I will have 4 dates to enter. I used to have Excel 2007 with Calendar Control and used to work fine but had to make multiple calendar to control individual Textbox in multiple Userforms.
I am looking for a way to, If a userform is selected to trigger only the textbox name that is within that userform. If I add more entries the code adds automatically the date from the previous userform in the next one.
[Code].....
I was thinking that this would work but it still adds the date to my frmdashboard userform if I am in my frmdata userform.
Is there a way to trigger only the userform that I am currently in?
Using Excel 2013.
Note: I also have a "Clear All" VB Code that will delete every entry in my userform on Initiate but I am looking to not go this path.
View 5 Replies
View Related
Dec 20, 2011
Using MS Query in Excel, I've created a simple query that pulls its records from an SQL dbase. Here's the statement:
SELECT uvVisit.FacilityListName, uvVisit.DoctorListName, uvVisit.Date, uvVisit.PatientVisitId, uvVisit.PatientLast, uvVisit.PatientFirst
FROM CPS.dbo.uvVisit uvVisit
WHERE (uvVisit.Date Between ? And ?)
ORDER BY uvVisit.FacilityListName
The query runs fine and prompts the user to enter beginning & ending date ranges for the visit date when executed. So far...so good...but, this requires me to manually insert a line in Excel above the 1st record and type in something like: "For Date Range: MM/DD/YYY - MM/DD/YY" to denote the date range that the qualified records fall into (something the user wants to see).
However, I'd like to find a way automatically preface and display in the report's output (perhaps as the 1st line of the report in Excel??) something similar to what I'm already typing, and have it pull the beginning and ending MM/DD/YY values from those supplied by the user in the parameter.
View 3 Replies
View Related
Oct 8, 2008
I'm trying to highlight a bunch of cells in clusters not close together, and then invert the selection so that all cells BUT these are selected (therefore capturing all possible cells that someone could scroll to). In XL03 you could go to File > Invert Selection and it would work fine. But in XL07 which I have now, there doesn't seem to be a way to do it. Also, when using ctrl^a to highlight everything, and while still holding control in XL03 you could de-select specific cells, not so much in 07. I need to know if there's a way to do it with no macros involved. If not, I need a macro to do such a function on any given worksheet.
View 6 Replies
View Related
Dec 9, 2008
I have selected a range in a worksheet using VBA. How can I now code so that if any cell in the selection has a value greater than 0 , then
Put up a Message. I do not know visually what has been selected, and it will change from sheet to sheet so I cant hard code the range.
If all cells have a value of nothing or zero, I will delete the entire selection.
If any row has cells with a value > 0, I will keep it and move to the next row
Then, delete all rows where all cells have a value of nothing or 0
View 9 Replies
View Related
