Computer Support Forum

Solved: excel length of date field

Question: Solved: excel length of date field

the answer to this might of been found with a search but hey i'm lazy today and wanted to see who the excel masters are

using excel 2007, cell a1 contains an array formula (CSE) like so - {=MAX(LEN(A2:A470000))}
cells a2 through a470000 contain dates such as
12/12/2011
etc, you get the idea, i need the formula or something else entirely to return 10 not 5

thanks in advance

Relevance 100%
Preferred Solution: Solved: excel length of date field

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Solved: excel length of date field

i'll answer if myself thanks to another web site, Mike these guys are slackin' 'round here it looks like

=MAX(LEN(TEXT(A2:A470000,"m/d/yyyy")))

exit celll with ctrl, shift, enter

1 more replies
Relevance 84.05%

Hello,

I have a large report of application forms which have been submitted including the date when they were submitted however the dates are formatted incorrectly.

1. Although the cell formatting is dd/mm/yyyy the data is being shown in mm/dd/yyyy which means the dates are incorrect (e.g. 2nd January would read as 1st February.)
2. As a result of this dates which are passed the 12th of a month are not accepted as dates (it reads the day figure as months and there is no 13th month etc)

I have tried to use the text to columns function but that makes no difference and I have tried to extract just the dates but I've had no luck.

Can anyone help, I've attached a small sample of the data.

Thanks

Daniel
 

Answer:Exported date field not recognized as date in Excel

Where does the data come from?
What do you get if you have no cell format?
 

3 more replies
Relevance 78.72%

Can someone help please?

I'm setting up a query in Excel from an SQL database. How do I (or what do I) do to ensure only todays data is shown - I suppose I'm looking for an"=TODAY" type thing but don't know enough to be able to do it.

Thanks in anticipation.

Moll )
 

Answer:Excel query on Date field

it can depend on how the date system is set up but the following page has pretty much every SQL date function going

http://www.connx.com/products/connx/Connx%208.5%20UserGuide/sql_date.htm
 

1 more replies
Relevance 78.31%

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Code:
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

Answer:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

16 more replies
Relevance 77.08%

This one has got me. Has anyone figured out a way to do this easily? I can create a new column and extract the right characters and make it work ... but just wondering if there is a quicker way out there!

Thanks in advance for your time/help ...
 

Answer:Excel: Extract hour ONLY from a date/time field

Ok, got it to work ... but not sure if there is a better way.

I created a 2nd colummn and used the =RIGHT(D107,5) to isolate the time (10:32) from the date/time field (JAN 12,2014 10:32)

I then created another column and used =TIME(HOUR(C107),0,0) to pull out the hour ONLY in 10:00 AM format.

Is there a quicker way out there?
 

2 more replies
Relevance 75.44%

Hi, I was kindly helped my AJ on this site to end up with

=SUMPRODUCT(((A7:A18)>=A6)*((A7:A18)<=B6)*(B7:B18))

for the folowing:
2 colums, 'A' with the year (currently as plain number) 'B' with a simple number. There are roughtly 5/8 entries for each year, from 1989 to 2008. I need to sum 'b' for a range of years, (say 1990 to 1995), I can do it for one year value but not more.

but the further problem I have is:
I have a list of 160 rows with the year being the key data field and selecting various ranges say 1995 to 2006.

I need to capture the data in a way that I need to keep historic info for years gone (2006,2007) but then alter the sheet (remove a row if no longer relevant) for current and future years.

I was thinking of having a final column with a "year removed field" and the main formula above being dependant on that for inclusion, make sense?
Many thanks
 

Answer:Excel 2003 - Selecting data within a date range with a further logic field?

8 more replies
Relevance 70.52%

I use due dates on my Outlook tasks. When I customize my Tasks list (in the Calendar view) to group by due date, I just get too many groupings (one for each due date!). I would like to know if I can create a custom field called 'Due Month' and/or 'Due Week', extracted from the 'Due Date', so I can view my tasks by what is due during a given week or month. If the answer is yes, any suggestions on the technique I would use would be greatly appreciated.
Thanks in advance,
Mac
 

More replies
Relevance 69.7%

I need a date field changed into a text field of YYYYMMDD so for example the date of birth field is 3/17/1953 and I need that converted to 19530317 in the Excel spreadsheet. Thanks.

Answer:I need a date field changed into a text field of YYYYMMDD

under the format options.

5 more replies
Relevance 69.7%

I receive periodic Word (.rtf) documents from a property rental agency whenever my property is rented. The document contains the dates rented, the renter's name, etc. There is a "date field" in each document which automatically shows today's date, rather than the date the doc was created and sent to me. If I want to forward the doc to the renter a week later how do I stop the date from updating when I send it and stop it from updating when the renter opens the file?
 

Answer:Solved: Stop Updating the Date Field

6 more replies
Relevance 69.29%

The company I work for has asked me to work with the DB (as you may well guess in this economy I now have to learn how to work with Access DB. This DB was created by someone who had a book “Access 2000 for dummies” in 2002, I have since then converted the 2000 DB to 2007 accdb format. That being said, in a form that is used to track records-TS# is the primary key, they want a YES/NO or actual name- Completed *check box for yes and blank for no*. I created the check box as you may know that was easy enough. The problem comes in when I go to the next record the box remains in whatever state it was on the previous record and does not show up anywhere else. I am sure this is likely because there are steps that I am omitting or not aware of. Any help offered would be amazing as I continue to work on this train wreck.

-Mike
 

Answer:Solved: Access 2007 YES/NO field with time/date

Mike, the Check Box field has to be in the Table that the Form uses as it's Record Source. The Check box has to be "Bound" to that table's field, i.e. it's "Contorl Source" of the check box in design view must be the table field.
 

2 more replies
Relevance 69.29%

I have a field that is date/time, formatted to display the long date (which starts with the day of the week). This has been extremely useful for some append, update and delete queries.

However, I would like to be able to do an update query based on the day of the week for each record. I know that the date/time field is stored as a number so I can't search for any string of letters. But, is there a way to take the long date format and copy what's there as a string so I can then extract the day of the week out of it? Otherwise, I guess I'm stuck with adding a separate field for day of the week, and manually inputting it. Seems like such a waste.
 

Answer:Solved: Access, Date/Time Field to String -- is it possible

7 more replies
Relevance 69.29%

from email address removed by Dreamboat

I have a database with a date field; 'Birthday'.

I want to take the month info from the date field and have access put it into another (adjacent) field. Ie;

Name:
Addr:
Birthday:
Month of B'day (New Field)

Also I want to be able to query the birthday field from previous data base tables which do not have the month field and make a table with the month information extrapolated to a new 'Month of B'day' field.

(Month([Birthday])) does not work.

I have printed a report with the 'Month of B'day" info extrapolated to the report but it is not useful because the report does not sort by the new information.

I know these must be possible but . . . . . AAAaaagh I'm going nuts.
 

Answer:Solved: Extrapolate Month from an Access date field

16 more replies
Relevance 69.29%

Hey everyone,

I'm having problems with Access, where I have a date/time field, which stores a month and a year (successfully), but still automatically assigns the first date of the month in question.

I used the following input mask (00/0000;;_) for the date field, and so far I haven't had any troubles with putting in a month and year. So far so good. But when I complete the date and move on, Access changes it to a full date. e.g. 06/2010 --> 01/06/2010.

Now, I'm actually fine with this part, my only problem is that I don't want it to do this in forms (both datasheet and normal view) or reports.

I heard something about using a Format (http://office.microsoft.com/en-us/access/HP010990151033.aspx) to adjust the display, but I can't seem to get that to work. Is there any other way to tell Access how to display the date field, like what can be done in Excel?

This is rather urgent as I have a project due tomorrow and this problem (as well as others) have now been reached and I'd like to solve them as soon as possible.

Any help is appreciated, and I look forward to your responses.
 

Answer:Solved: Urgent: How do you format the DISPLAY of a date field?

8 more replies
Relevance 68.47%

My lady wife is having a small problem with a formula she is trying to use in an Excel spreadsheet.

She is trying to make a formula that she can copy and paste into various spreadsheets she utilises to organise her staff's workflow.
The five staff deal with about 30 suppliers and she wishes to be able to associate each supplier with a particular member of staff, so, for example, if column A is 'Suppliers'and column B is 'Staff Member', a formula using the IF function is placed in column B using the following syntax:
=IF($A1="aaaa","Rashmi",IF($A1="bbbb","Laura",IF($A1="cccc","Linda",IF($A1="dddd","Denise",IF($A1="eeee","Mark","")))))Click to expand...

This works just fine with only a few suppliers, but if the string is lengthened to more than about 9 suppliers, as here:
=IF($A1="aaaa","Rashmi",IF($A1="bbbb","Laura",IF($A1="cccc","Linda",IF($A1="dddd","Denise",IF($A1="eeee","Mark",IF($A1="ffff","Rashmi",IF($A1="gggg","Laura",IF($A1="hhhh","Linda",IF($A1="iiii","Denise",IF($A1="jjjj","Mark",IF($A1="kkkk","Rashmi",IF($A1="llll","Laura",IF($A1="mmmm","Linda",IF($A1="nnnn",&quo... Read more

Answer:Solved: Excel formula length problem

11 more replies
Relevance 68.47%

I want to add a validation rule to a date/time field in Access 2010. This worked in 2003 and I'd like to know why it won't work now.

The rule is

Is Null Or <= Date()

I have entered this through the expression builder as well as typing it to avoid typos.

The error message is:

Unknown function 'Date' in validation expression or default value on 'Artists.Date edited'.

I am doing this at work: we are running Windows 8, Server 2012, Access 2010 via virtual desktop.

Any help would be gratefully received!

Thank you
Alison
 

Answer:Solved: Access 2010 date field validation problem

Alison, welcome to the Forum.
I think you will find that this is an Access problem of Access 2010.
You may be able to overcome it by using Now() instead of Date().
If not I can provide you with some simple VBA code that will do the same thing that may work.
I have also seen advice to open a new 2010 blank database and import everything in to it to solve this problem.
 

2 more replies
Relevance 68.47%

I've searched the forum and can't find the help I need...perhaps I'm not using the right keywords.

Anyway, I want to automatically have a time stamp entered in a field named "CancelDate" if a yes/no field named "Cancel?" is checked. On the form I use an action button for that field.

I'm new with VBA, but after some research I entered this code in the AfterUpdate line in Properties:

Private Sub CancelDate_AfterUpdate()
If Me.txtCancel?.value = 1 Then Me.txtCancelDate = Now()
End If
End Sub

...and it doesn't work. It seems to hang up because of the question mark. I get a Compile Error: Expected:Then or GoTo.

Can anyone help me?
 

Answer:Solved: Access 2007 Record Date When Field Updated

10 more replies
Relevance 68.47%

Good day guys

My puzzler today (and has been for ages) is this:

In my Access report, I pull in "StartDate" and "EndDate" fields from the calling form and wish to display them as part of a report subtitle.

I therefore have a field called "DateDetails" in my report header whose ControlSource is:
Code:
="Records Selected between " & Forms!Reports!StartDate & " and " & Forms!Reports!EndDate
The dates actually derive from a table and the date format for both fields in that table is "General Date".

The result is that the report shows the following as an example:
Records Selected between 17/05/07 12:00:07 and 10/08/07 12:00:07

BUT what I want is the following:

Records Selected between 17/05/07 and 10/08/07

or even nicer

Records Selected between 17-May-2007 and 10-August-2007

Can this be done in such a control?

Chris
 

Answer:Solved: Access 2003 - Date Format in a Calculated Field

7 more replies
Relevance 68.06%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 67.65%

When items are loaned out we want them returned in 30 working days (approximately 6 weeks I'm not going to bother trying to calculate holidays) but to only come back on a week day Monday through Friday.

Field Name ReturnDate_Fld this needs to be calculated as 30 working days from the current date

or should I not worry about the extra effort and use the approximate by adding 42 to the BorrowDate_Fld (plus 42 days = 6 weeks)

Should I worry about this in a form or only set it as field in the report/query?
 

Answer:Solved: Access 2010 Set a date field to today plus 30 working days

16 more replies
Relevance 67.24%

There must be a way to fix the text field lengths in Word for Windows 2002, so that the form does not change/collapse when an entry is made with fewer letters than are allowed in the field.

Can someone help me with this?
 

Answer:Setting/fixing field length in Word 2002

My sister had the same problem and she got around it by putting the fields in tables. She then set the tables as transparent.

Hope this helps,

MBN
 

3 more replies
Relevance 66.83%

Hello,
Firstly, let me apologise, I'm a complete Access noob & am trying to teach myself as I've got no one to help me in person.

Ok,
I'm trying to build a Time Sheet database that should record daily duties via an entry form:
"frm_Work_Hours"

This form updates an underlying table:
"Work_Hours"

The date field of the "Work_Hours" table is:
"Date_Worked"

However, workers get paid fortnightly and all their entitlements, allowances & overtime are calculated on these fortnightly periods. To be more specific, the workers are paid for a certain amount of overtime for each fortnight upfront & when they exceed that, then they are entitled to all excess time back in TIME OFF. So it's pretty important that each day's duties are assigned to the correct fortnight period so that their overtime & so-forth are calculated correctly.

Each fortnightly period is numbered according to year. For example, each year there are 26 fortnightly periods, so as an example today (3rd October 2013) falls in period 2013-19 (23rd September 2013 to 6th October 2013).

I've created a table called:
"tbl_Pay_Period"

Which has the following Fields:
"ID" (Auto-number)
"Period_Start" (date - the first day of each new fortnight period)
"Period_Number" (number - eg: 2013-19)

Back in the "Work_Hours" table, I inserted a new (Number) FIELD called:
"Pay_Period"

and set ... Read more

Answer:Solved: MS Access - How to get a field auto-update based on date entered in a form

9 more replies
Relevance 66.42%

Have created some basic online forms. Now I am tasked with displaying each entire field length (visually somehow) so the user can see approximately how much room they have to enter test. Examples:
Name: Field length highlighted or shaded
Location: Underlining would work to indicate field length
Phone: ( Parenthesis might work )

If I type "Enter Name Here" in Properties, under Default Text, only "Enter Name Here" is shaded. The shading does not extend to the maximum length I selected. The field extends that far, but no indicator of the entire field length. Thanks to everyone for being patient. Attempted to be specific as my question seemed unclear in other unrelated forums. This forum/posting/threads, etc. is new to me. I am 62, so go figure!

Answer:Forms/Word 2007/Display entire field length

Welcome to the Seven Forums.
This is unfamiliar to me, have you tried enter spaces in the chosen field?

6 more replies
Relevance 66.42%

Tech Support Guy System Info Utility version 1.0.0.1
OS Version: Microsoft Windows XP Professional, Service Pack 3, 32 bit
Processor: Intel(R) Pentium(R) 4 CPU 2.80GHz, x86 Family 15 Model 4 Stepping 1
Processor Count: 1
RAM: 1015 Mb
Graphics Card: Intel(R) 82865G Graphics Controller, 96 Mb
Hard Drives: C: Total - 38154 MB, Free - 21206 MB;
Motherboard: Hewlett-Packard, 090Ch, , MXL5140C4H
Antivirus: Trend Micro OfficeScan Antivirus, Updated: No, On-Demand Scanner: Disabled

Wow! Hope I'm in the right place for an answer! My expertise in this area leaves much to be desired....here goes.
Using Word 2007 to create online forms and need each content control field to indicate its length visually(greying, shading, underlining, whatever). This is to let the person filling out the form how much space is available for their response. Am currently trying to make it work inside a table also with no success. Hope you can help. I am lost at this point. Thanks so much for your time.
 

Answer:Visual indication of field length in online 2007 forms

You would need some sort of java based counter to use as an add-on to your web site.
 

1 more replies
Relevance 66.42%

I accidentally formatted the date cell & it only accept numbers

Answer:how to correct a date field when you enter a date a number a

I don't know what you mean by "it only accepts numbers".Why can't you just format it as a date again?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 65.19%

Help! This should be easy but I can't get it to work.

I have a report that must group by Trip# and sort by ascending activity date all of the various activities that took place on multiple days of the trip.
Further, the trip with the earliest date must come first regardless of the Trip#.
I've accomplished getting the earliest starting date trip first and grouping its detail with a unique string made up of the earliest date of each trip formatted to YYYYMMDD+Trip#. This prints a trip total and works fine.

But the daily activity for the trip is out of date sequence.
Adding a Date field to the 'Sorting and Grouping' insists on breaking on and printing a sub-total for each of the trips activity dates.
Without the sorting & grouping by date the trip records are out of sequence even though the data source query is sorted ascending by activity date.

(see attachments that show the above)

The only solution I can think of is to create a sub-report sorted by activity date.

Does anyone have any better ideas?

Jim Shannon
JES Computer Systems
Access Developer.
 

Answer:How sort by 2nd field (date) without grouping when date changes?

7 more replies
Relevance 63.55%

Hi, I need help with a macro. I want to copy the contents of the cell above into the cell below if the cell below is blank.

A1 1234
B1
C1 5678
D1
E1 2345
F1
etc.

so it looks like this after the macro runs.

A1 1234
B1 1234
C1 5678
D1 5678
E1 2345
F1 2345
etc.

A1 is line 1 of column A. My apologies that I don't know how to copy an excel into my post.

Thanks in advance for your help.
 

Answer:Solved: excel help if field is blank copy contents

Hi

Is a macro absolutely necessary? This can be done by using a blank column and, starting in row 2 enter this formula

=IF(A2="",A1,A2)

Copy this to the end of your list then copy the new column and PasteSpecial/Values to column A.

A few extra steps than a Macro but much easier to maintain. If you really need a macro let me know and we can whip something up.
 

2 more replies
Relevance 63.55%

I have an excel file with a column with values like 100, 200, 500, 500-02, and 500-04.
Access wants the column to be a number because 500-02 is not one of the first records.

If I use the acImport or acLink I get the same results. Which is an error importing (I get all the data just not the text values in the column) or a null value in the link when view from Access (even though you can open the spreadsheet with excel and see the true data). If I use the import Wizard and change the Field to text it that data is fine.

I also tried acImporting to an existing table with the field set a text but it changes it back to numeric and screws up.

I have a vision impared user and I need a button click to get this data into access.

Thanks for reading my post.
 

Answer:Solved: ACESS: import excel field as text

7 more replies
Relevance 62.32%

I've upgraded from Excel 2000 to 2007 and cannot get the pivot table field list to show. I activate the pivot table and switch it from hide/show either by right clicking or from the toolbar but still no joy. I''ve looked all over the spreadsheet to see if it hidden anywhere but cannot find it. Any suggestions? If it is hidden somewhere can I move it via vba code?
 

Answer:Solved: Excel 2007 Pivot Table Field List Missing

16 more replies
Relevance 62.32%

I wish to know if it is possible to update a field on one sheet with the last entry of another sheets column?

as info is added to one sheets column the data on the other sheet is automaticall altered to show the last entry in the first sheets column where i entered the data in the first place
 

Answer:Solved: EXCEL: update field on one sheet with the last entry of another sheets column

16 more replies
Relevance 59.45%

I am trying to create a template to calculate the expected number of days for my shipment to arrive and I am cracking my head trying to put in the day as well i.e. for today [Aug-5-2002 Mon] How do I format it?

Anyone can help me? Thanks.
(btw, is it possible in the first place?)
 

Answer:[SOLVED] Day & Date in Excel 97 SR2

Right click the cell and select format cells, select custom from the list and put in mmmm-d-yyyy dddd and the resulting date will be in your format.
 

2 more replies
Relevance 59.04%

I have a timesheet type Excel workbook. The week is official over on Saturday. When I open a blank timesheet, I would like for Saturday's date for each week to be automatically inserted. I also need a way for the date not to be updated, if the workbook needs to be reviewed at a future date.

Any help with this would be greatly appreciated.
 

Answer:Solved: Excel Enter Date

13 more replies
Relevance 59.04%

Hello, I am having difficulty getting Excel to sort by date with the following dates:

Column A
09/20/2012
09/21/2012
10/20/2012
02/20/2012

When I select "sort newest to oldest" it sorts the dates this way:

10/20/2012
09/20/2012
09/21/2012
02/20/2012

I have verified that the dates are not formatted as text and have altered the way the date is shown, trying it without the zero and with the zero and nothing changes.

Help!
 

Answer:Solved: Excel Sort by Date

I think I discovered my error, the dates showing 02/20/12 should be 02/20/13!
 

2 more replies
Relevance 59.04%

I have the following code
Code:

Dim EndDate As Date

EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)

If ws.Range("K4") <> "" Then
'Do Nothing
[B]ElseIf Date = EndDate Then
ws.Range("K4") = EndDate[/B]
Else
ws.Range("K4") = Date + (7 - Weekday(Date, 1))
End If
 

Answer:Solved: Excel VBA Enter Date

13 more replies
Relevance 59.04%

I have an elaborate Excel file (a weight reduction program) that includes a cell that shows the first day of a month (dd-mm-yy). Based upon which month of the year is in that cell, I want to make an IF statement that uses the number of days in that month (28,30 or 31 days). If I copy that cell and format it as a DATE per the mm format, it just prints the first letter of that month. Therefore, if there was a "J" in that second cell, the IF statement would not know if it was Jan (31 days), June (30 days) or July (31 days).

Anyone have any ideas?

Thanks
[email protected]
 

Answer:(Solved) Excel Date format

Hiya

Moved you to Business Applications, as you may get more help here

eddie
 

3 more replies
Relevance 59.04%

Hello,

I have read through a lot of posts and have not come across one that actually fits my issue. I am trying to come up with a macro or a formula that will do the following:

Date
27-Oct-08
28-Oct-08
29-Oct-08
30-Oct-08
31-Oct-08
3-Nov-08
4-Nov-08
5-Nov-08
6-Nov-08
7-Nov-08
10-Nov-08
The date (the word Date is A1) is in colume A and the row is what is changing (ie A2 (27-Oct-08), A3 (28-Oct-08), A4 (29-Oct-08), etc) I don't want the weekends if possible. I have tried a formula but something is wrong with it "=DATE(YEAR(A2),MONTH(A2),DAY(A2)) - IF(AND(MONTH(A2)=2, DAY(A2)=29),1,0)" It makes every day (every row the same value, doesn't increament) The IF statement is suppose to check for leap year. The =Today() changes all the days to be the same, which I don't want. Can anyone modify this so that it works or auto puts the date in the next row?
Each row gets a new date, I don't want to have to hand type in the date everydat if I don't have to.

Thanks in advance.
 

Answer:Solved: Excel auto date

Hi there, welcome to the board!

If you have "Date" in A1, and your start date entered manually into A2, you can put this in A3 and copy down...
Code:
=IF(WEEKDAY(A2)=6,A2+2,IF(WEEKDAY(A2)=7,A2+1,A2))+1
HTH
 

2 more replies
Relevance 59.04%

OK...since there have been no responses to me 2 previous posts, I hvae another question:

I currently have a formula (graciously provided by bomb #21) that calculates the number of years between the current year and a predetermined date.

However, it is based on the calendar year and not the current date. For example, between now and July 30, 2000 would be 10 years. However, the calculates still result in 10 years even when the date is changed to Feb 1, 2000 the result is still 10 years because the current calendar year has not changed.

Here is the formula:

YEAR(NOW())-YEAR(F6)

I need a formula that uses the current date as the threshold for the calculation. I've tried combinations of the DATE, MONTH, YEAR, DAY, etc, functions but can't get anywhere.

Anyone have any thoughts?

Regards,

TBaker14

 

Answer:Solved: Excel Date formulas

there may be a better solution - but on first look, I would test the Month () and if month(now) > month(f6) then subtract one from the year calc - and then also do the same for Day if Month(now) = month(f6) not infront of an excel PC right now - but that was my immediate thought - but I'm sure there's a better way just using dates .
 

3 more replies
Relevance 59.04%

Good afternoon all,

Long time no seek help!! (cos I learnt so much last time )
I have a worksheet containing a list of stock i.e. Col A = Date; Col B = Product Description; Col C = Amount of stock

How can I (hopefully using a macro) end up with a report containing the information in Col A; B and C but sorted/sub-totalled as follows :-

Stock older than 18 months
Stock held in store for 12 - 18 months
Stock held in store for 6 - 12 months
Stock held in store 0 - 6

Does this make sense - Is this possible?

Thanks if you are reading this !

Moll
 

Answer:Solved: Excel Date range

16 more replies
Relevance 59.04%

does anyone know how i could change a column in a table that currently has dates in the m/d/yyyy format to mmddyyyy

here is an example

4/20/1991 to 04201991

this series of table contains hundreds maybe thousands of dates and I have to have the state report done by the end of the day any ideas would be appreciated

Thank You,
Andy
 

Answer:Solved: EXCEL Date formats

Dump it into Word and Use replace the / with nothing. Replace is gotten to via "CTRL-H"
 

3 more replies
Relevance 59.04%

Hi guys,

Pretty new to Excel really, know the basic stuff but the intermediate stuff is beyond me for now. Hence this post.

What I want to do is this:

Have a field on my spreadsheet that contains a fixed date say 06/06/2008 and this cell would be green or a cell next to it would be green.

Now as the date reaches say 28 days in the future, so 04/07/2008 I want the colour to change to red.

Is this possible and if so, how do I do it???

Thanks,

Peaker.
 

Answer:Solved: Help. Need help with date triggers in Excel

8 more replies
Relevance 59.04%

Is it possible with Access 2010 to have a date field that would change to current date whenever another particular field is modified. For example, we have comment field and we would like to have another date field that would change to the current date when the comment field has a change.
 

Answer:field modified date

Yes you can do it with simple VBA code, you could also add it to the comment itself, so it appears at the beginning of the comment.
 

1 more replies
Relevance 59.04%

I work with MS Access 2003.

I am creating a query to show all contracts set up in a certain year that have been cancelled in that specific year. This "Cancelled" field is not a yes/no field, but includes the date on which it has been cancelled (always starting 31/12/YYYY).

First, to show me the contracts set up in a certain year (say 2008) , i created a parameter under the "Contract Year" field, asking which year you want to see (in this case 2008).

I wish to add a criteria that then shows cancelled contracts at the end of that specific year, so 31/12/2008. So whether it is "2008" or another year is dependent on the value in the "Contract Year" field.

I tried the following as criteria for the Column "Cancelled":

#31/12/[Contract Year]#

to try and have it take the year mentioned in that field.
But it doesn't work. Access sees is as a second parameter, instead of a field name.
I tried adding LIKE to it and the &-sign, still no luck.

Is it possible to use a field name in a date criteria and if so, how could i do it?

Thank you very much in advance for your help.
 

More replies
Relevance 59.04%
Question: Update date field

Thanks in advance for your help!

I used to know this and I did a search and couldn't find the answer.

How do you update a field to a date either 6 months away or 12 months away for the current date in that field?

Thanks again!
 

Answer:Update date field

6 more replies
Relevance 59.04%

Not sure how to go about this. I am not a programmer by any means but need to make a program.

I have a text file that is in what is called BAI format (bank standard format to send info). Basically this is a CSV file with a header. Right now I use a mail merge file to update the date field. I am looking to automate this so I would only need one "dummy" file and have a program that will update the date field automatically. The file is opened with notepad. I have done some VB but not a lot. I am looking for the best way to go about doing this. I tried to get some of the programmers to do it but no one wants to worry about supporting it, so I am taking a crack at it.

All help is very much appreciated. One idea I had is maybe a macro in excel but not sure if it would mess up the formating. I will try to get a copy of the file I use and post it.
 

Answer:Updating date field

6 more replies
Relevance 58.63%

I have a table titled time_slips used to track the amount of time an attorney spends with a member. In the time_slips table there is a field titled reference_id which is a combo box pulling it's displayed information from another table titled references. Also in the time_slips table is a field named details which is native to this table.

My question is, can I have the details field auto-populate "no charge" when a certain reference id is selected (i.e. 300)?

Thanks for any help you can provide.
 

Answer:Solved: Access Auto-Populate a Field using a field from another table

15 more replies
Relevance 58.22%

Hi I realise that this is a common thread but I couldn't find a way to make it work for me in particular.

I have some cells that i want to add dates to in the future. they will contain a date that i want to add 14 days to, so that i know when to delete something two weeks later.

the formula i have used is =DATE(YEAR(Vicki!C16),MONTH(Vicki!C16),DAY(Vicki!C16)+14)

but because that formula is operating on a cell (in a seperate sheet) without a date in it currently, the cell displays 14/01/1900

I would like the cell to display as empty/clear until there is a date in the cell I want to add the 14 days too.

Hope that make sense
Any help gratefully received
 

Answer:Solved: Excel displaying a date when i dont want it to

Hi tiggly, welcome to TSG.

You need to add an If statement so that Excel will check whether the cell is blank or not. Try something like this:

=IF(Vicki!C16="","",DATE(YEAR(Vicki!C16),MONTH(Vicki!C16),DAY(Vicki!C16)+14))

Hope that helps.
 

3 more replies
Relevance 58.22%

Now I need to calculate a date from a given date using 13 workdays prior if the given date is a Monday and 14 workdays prior if the given date is a Wed.

Is that possible?

Thanks
 

Answer:Solved: Excel 2003, Calculating a date using ifs

This will give you the dates for those and a blank for any other days:
Assuming your date is in column A
=IF(TEXT(A1,"dddd")="Wednesday", A1-14,IF(TEXT(A1,"dddd")="Monday",A1-13,""))
 

1 more replies
Relevance 58.22%

I need to use formulas to caluculate a given number of workdays before a given date.

For example:
A project is due on August 31

I need to figure
2 workdays before that date
7 workdays before that date
13 workdays before that date
26 workdays before that date

Can anyone help?

Thanks
 

Answer:Solved: Excel 2003, Calculating a date

7 more replies
Relevance 58.22%

Down Under we show our dates as dd/mm/yy. I have a user who types in 3/4/00 and the system automatically reverses the date to 4/3/00. I have checked the properties on the regional settings, and the date options in Excel are also correct. I have tried, in Tools/Options/Calculations to change the date format to 1904, but the system wont accept it.

Any ideas for resolution appreciated.

Ms. Kumara
 

Answer:(Solved) Excel date format wrong

In my 4 years in customer services for a well known computer company, I have never seen that before.

Microsoft Excel does not control the dates nor the settings, ie currency etc. Which version are you using?

Try using another Microsoft program like Word to see if you have the same problem, if not, then you would need to speak to Microsoft in New Zealand.

Also, double check to see under Regional Settings in Control Panel, in regional settings tab, make sure the setting is English (New Zealand), and press apply (if it is not greyed out. If it asks you to restart, press no and go to the Currency and Date tabs to make sure that the setings are for New Zealand.

If that fails, then you can *force* the machine to your own custom settings, by changing the values.

If all else fails, then it could be Windows itself.

Please email me to let me knowhow you got on.

James
 

2 more replies
Relevance 58.22%

I have a cell with a date value of "4:00:00 PM" and I'd like to be able to hit the + key and increment it by 1 hour (so it would become "5:00:00 PM", then "6:00:00 PM", etc.). Is that possible?

P.S. I'm using Excel 2003.
 

Answer:Solved: Excel: Can I increase date/time with +/- key

9 more replies
Relevance 58.22%

I'm looking for a formula for excel, but can't seem to fine tune one, or find an existing one.

The spreadsheet is for applications. If initial contact happens on say 7/20/2010 and the applicant has 5 days in which to respond, for example. I want a column that posts the due date for follow up automatically, and then also a column that says either NO or YES in regards to Follow Up Needed that also updates automatically based on Date Of Follow Up having passed.

I hope this makes sense. I'm going to attempt to put an example below.

Application Date Applicant Name Date Of Follow Up Follow Up Needed
7/20/2010 John Smith 7/26/2010 YES
7/25/2010 Jane Smith 7/31/2010 NO

EDIT: Well, it won't maintain the spacing, but that should give you an idea of what I mean.
 

Answer:Solved: Excel Formula if a date is exceeded

11 more replies
Relevance 58.22%

I am trying to enter a date in a column as yymmdd but when I enter 09 as the year is drops the 0. I tried using different formats but that is not working either. For example, I changed the cell format to yymmdd and when I entered 090120 it displays 460926. Can someone tell me what I need to do to get it to display as I need?
Thanks!!
 

Answer:Solved: Excel 2002 Date Format

6 more replies
Relevance 58.22%

hi, the goal is to calculate the difference between the 'year' portion of 2 date entries in different date formats; example:
if cell 1 contains:
12/26/2000
and cell 2 contains:
31-Dec-11

what is the formula to get the result?
 

Answer:Solved: excel - date difference in years

6 more replies
Relevance 58.22%

I use a lot of Excel macros at work, often formatting pre-fab reports for aesthetics, etc.

I'd like to be able to insert the current date as a part of the macros. I'm sure this can be done, but I don't yet know VBA well enough to write it out myself.

Any ideas?

I'm using Windows XP w/ SP2 and Excel 2003
 

Answer:Solved: Inserting date with Excel macros

See the code below. If you want to also include the time in addition to the date you can use replace the word Date with the word Now
Code:
Range("A2").Value = Date
or
Code:
ActiveCell.Value = Date
Regards,
Rollin
 

3 more replies
Relevance 58.22%

I have a user who is trying to update an Excel spreadsheet somebody sent them. In one cell, there is a reference to the date located in another cell. However, the way it is formatted, the date in the cell displays as just a number instead of the date ("September 22, 2010" displays in the 2nd cell as "40443".

In the second cell, there is some text along with the formula referencing the original cell containing the date. In the formula bar, it looks like this: ...Los Angeles, California 90013, on "&AP29&"

Yet in the spreadsheet, it displays like this: ...Los Angeles, California 90013, on 40443

In the original/refenced cell (AP29), the text entered into the cell is 9/22/2010, and is formatted to display as September 22, 2010.

Any idea how to correct the formula to display the date correctly?

Thanks a bunch.
 

Answer:Solved: Date format - Excel 2007

Hi Traci.

It's because the underlying value of the date is a number, which is how Excel interprets it no matter how it's formatted. To change it to display the date, you have to tell it it's a date within the formula. Try this:

...Los Angeles, California 90013, on "&TEXT(AP29,"m/dd/yy")&"

Without seeing the whole formula in the second cell I'm not quite sure about the positioning of the ampersands and quotes, but play around with it a little. Let me know if it doesn't work.
 

3 more replies
Relevance 58.22%

My Challenge:
I have a table with 34 different date ranges representing individual rounds 1 to 34.

Start End Round
04/12/2009 05/15/2009 1

In another worksheet I have a long "to do list" of different tasks which have start and end dates. I would like to set this sheet up so that in a "Round" column, a formula would calculate which round each task is in, given its end date.

Task Start Date End Date Round
Final Material Review 31-Aug-09 31-Oct-09 0

I believe I may need to use either a VLookup or IF formulas, though from what I understand of IF formulas it would be far to complex with 34 different date ranges. I am not sure how to use a VLookup with date ranges in this senario.
 

Answer:Solved: VLookup in Excel Using Date Ranges

10 more replies
Relevance 58.22%

Tech Support Guy System Info Utility version 1.0.0.1
OS Version: Microsoft Windows 7 Professional , Service Pack 1, 32 bit
Processor: Intel(R) Core(TM) i5 CPU M 520 @ 2.40GHz, x64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2993 Mb
Graphics Card: Intel(R) HD Graphics, 1272 Mb
Hard Drives: C: Total - 234274 MB, Free - 193142 MB; E: Total - 2048 MB, Free - 2010 MB;
Motherboard: Hewlett-Packard, 1722, KBC Version 29.36, CND037022T
Antivirus: McAfee VirusScan Enterprise, Updated and Enabled

I would like to add a formula that would automatically calculate a date field from one cell and add three years. See example attached, Thanks in advanced
 

Answer:Solved: Calculating Date Fields in Excel

7 more replies
Relevance 58.22%

I have a tab delimited .txt file. One of the fields has 5-2056 in it. When I open the file in Excel, it gets changed to May-56. How can I stop this? I can't edit the data, and I can't format the cell (it's not an actual cell/spreadsheet).
 

Answer:Solved: Excel, stop changing to date

If you want the 5-2056 kept as is, then do the following.
Select all the cells with data in
Click on Data, Text to Columns
Delimited, NEXT
Select the delimiters you want to apply e.g.TABS, NEXT
The 5-2056 should now be shown in its own columnand the heading should say general. If so just click on Finish
It should now be in its own columns and the 5-2056 will appear just as that.

hope that works foryou.

Come back if you need more clarity.
 

3 more replies
Relevance 58.22%

In Excel 2007, Is there a way show a date stamp of when data was entered into a cell? So when you hold the cursor over the cell it shows you the date the data was entered?
 

Answer:Solved: Excel 2007 date stamp

7 more replies
Relevance 58.22%

Hi all, I have a crazy problem with date entry in my Excel 2007 & it's driving me nuts!!

I have two dates (departure & arrival) that I copy in from a text file. the values are 15/03/2011 & 08/04/2011. (DD/MM/YYY)

When I copy these into excel the 15/03/2011 is copied in "as is" but the other date copies in as 8/4/2011 and is right aligned. If I right click both cells are formated the same.

However if I view them as long date formats I can see that 8/4/2011 is now displaying as Thurs August 4th while the 15/03/2011 remains the same e.g. 15/03/2011

Why is the date 8/4/2011 changed to August instead of April and what do I need to do to make it stay the same format as the other date 15/03/2011.

I urgently need anyone who can help to reach out to me.

thanks
Ray
 

Answer:Solved: STRANGE Excel date issue

You are set for the wrong date format. Your machine is using U.S. style mm/dd/yyyy instead of the other format dd/mm/yyyy. The 15/03/2011 date is actually an invalid date the way it is set.

Start>Settings>Control Panel>Language and Regional Settings

Change the short date format or the Format section (change English to English - Australian or UK or whichever is appropriate.

Kevin
 

3 more replies
Relevance 58.22%

I'm using ms office 2003 excel. I have a column date that is in a format of 20100410 and I would like to convert it to April 10, 2010. Any ideas?
 

Answer:Solved: change date format in excel

If the column is actually a date (not text) then highlight the column and go to Format, Cells. Select the Number tab and then click on the Date format. A list of formatting options will appear and you should find what you want.
If it doesn't work, do the same thing and select Number. If you actually have dates typed in, then 20100410 would show as 40278.00 - if it is text, it will show as 20100410.00.
 

3 more replies
Relevance 58.22%

Hi

I've searched several Excel sites, and I've found VB close to what I need, but I don't enough to make it work for me.

I have info in rows from A to J going across. Each row has information about a certain account. When anything in that row is modified, I'd like cell K to show the date modifed automatically.

I found a script that will do that but it only does A as the target cell and then puts the date in one cell over from the target. I need the target to be A thru J and the date cell to be K always.

Any ideas?

Here is the code I found on the Internet. Perhaps someone knows how to change it to do what I want.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by Barrie Davidson
If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub
Thanks
 

Answer:Solved: Excel - Auto date when modified

Hi, this appears to do it.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column > 0 And Target.Column < 11 Then
Target.Offset(0, 11 - Target.Column).Value = Now()
End If
End Sub
 

3 more replies
Relevance 58.22%

I have a macro that I want the user to input a date such as 12/08/08 and then conditional format column O for any cells containing date greater than the user's input date. The only thing it highlights is the header. I am not sure how to get the formula= MyDate to function correctly. Any help would be appreciated.
Code:

Sub TestUserInput()
'
' Macro1 Macro
'
'
Dim MyDate As Date
Columns("O:O").Select
MyDate = Application.InputBox("Enter a Date")
' MyDate = DateValue(MyDate)
MsgBox "The name you entered was " & MyDate

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:="& MyDate"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
 

Answer:Solved: Excel 2007 Date Variable

7 more replies
Relevance 58.22%

Sorry for the newbie question, but here goes:

I am using Excel 2007 and I have a spreadsheet that uses 2 dates -- the buy and sell date. I have a column that calculates how long we held the product. I use an IF statement to put a zero in the column if the product hasn't been sold yet which looks something like this:

IF(ISBLANK(buy_date),0,(sell_date - buy_date))

Now I want to summarize the "time-to-sale". I want to count all "0" sales (unsold), all sales between 1-30 days, 31-60, 61-90, through the whole year. The problem is, I can't use a macro because this spreadsheet needs to be "macro-free".

Suggestions?

Thanks,
gpence
 

Answer:Solved: Excel formula -- by date summary

7 more replies
Relevance 58.22%

In EXCEL (Office 2002) Is there a way to have a header that is auto matically updated to show the date the file was last edited(changed) ? . It should do this each time the file is edited(changed). Then when the file (.XLS) is emailed to someone and they print it, The last "edit(changed)" date will show .Currently the local date the file is printed is showing.
Manually editing the header is forgotten at busy times.
 

Answer:Solved: Show last edit date in Excel ?

7 more replies
Relevance 58.22%

Hi

I hope somebody can help.

I am attempting to set-up a spreadsheet to workout calculations based on working days (not Saturday & Sunday) only.

I can work it out using Saturday & Sunday, how do I exclude weekends from the calculation?

Thanks

Dave
 

Answer:Solved: Excel 2010 - date Calculations

use networkdays()
or
workdays()

see
http://office.microsoft.com/en-gb/excel-help/networkdays-HP005209190.aspx

http://office.microsoft.com/en-gb/excel-help/workday-HP005209339.aspx
 

3 more replies
Relevance 58.22%

Excel gurus,

Back again with another difficulty...

I have the following imported data in a single cell representing the date time and a variable

A1
06/25/2001 15:27 1.4

With the formula =LEFT(A1,16) and =Right(A1,4) I am able to extract the Date and time from the variable from the cell as follows.
A1 II A2 II A3
06/25/2001 15:27 1.4 II 06/25/2001 15:27 II 1.4

What I am having difficulty with is transforming the Date and time (06/25/2001 15:27) to the excel sequential numbers known as serial values (ie 37067.64375). What I want is the following:

A1 II A2 II A3 II A4
06/25/2001 15:27 1.4 II 06/25/2001 15:27 II 37067.64 II 1.4

I have tried formatting the data to date or to custom m/d/yy h:mm AM/PM or m/d/yy h:mm without success. Any suggestions?
 

Answer:Solved: Excel XP Date time difficulty

9 more replies
Relevance 58.22%

Hi everyone

I hope all are fine and reading my message in the best of their health.. I am stuck with a job to do on various dates where i have to email those candidates whome progress reports are due, which due dates are mentioned in the sheet.

Can some body please help me to come up with a excel automatic mail solution that an email should be generated via outlook without opening the file or even if needed opening the file. The email should contain

Dear Student

Your progress report / proposal defense is due on (due date),

regards,
this email should be generated and sent before one month and one copy on the date on which it is due.. the cc list also needed to be grabbed from somewhere, where cc of this email should go.

I shall be very thankful if somebody can look the attached file and help
 

Answer:Solved: Excel Automatic Email on due date

16 more replies
Relevance 58.22%

Hello:
I am in college , have a professor that thinks I understand excel , and have been sitting here for about 5 hours trying to do this and even the simplest things are not working for me.

Below are the directions given, I have used the AND function and filled it out but it does not give me what I need. I need it to look like this with the word valid beside the date, under validity. But it keeps telling me false or invalid when I fill out the AND function guide.

There is more directions listed for other things that I have to do and Have not been able to get correct either. I did get correct the cells that only had to list dates. But I have not gotten correct much more than that.

I am rather frustrated and really need any help anyone can offer.

User Input: employee data (cell a1)
Field Value Validity (cells a2,b2,c2)
Employee start date: 1/1/2009 valid (cells a3,b3,c3)
Employee end date: 12/31/2009 valid (cells a4,b4,c4)
Annual paid vacation days: 10 valid (cells a5,b5,c5)
Hourly pay: $10.00 valid (cells a6,b6,c6)
Hours per day: 8 valid (cells a7,b7,c7)
DIRECTIONS:

Employee start date: The date (B3) is valid if it is:
o on, or after, the &#8220;year begin&#8221; date in F3, and
o on, or before, the &#8220;year end&#8221; date in F4, and
o on, or before, the employee end date in B4

&#8226; Employee end date:
The date (B4) is valid if it is:
o on, or after, the &#8220;year begin&#8221; date in F3, and
o on, or before, the &#822... Read more

Answer:Solved: Excel: valid new employee date using AND

Well a couple of things. We won't do your homework for you. You must do the work. So no solutions will be provided to you. What I will do though, is try to give you some insight here so that you may come up with a solution on your own.

The AND() function is not helpful in validating dates, in fact that is a completely false statement. It is a logical function, which means it will have a boolean return (either TRUE or FALSE). The only difference between that and an OR() function is that all arguments inside the function must return TRUE for the function to return a TRUE result. With the OR() function, any one single item in the function must be TRUE for the function to return TRUE, whether that is all of them or just one, but if none of them are TRUE then the function returns FALSE.

Here are some examples...

=AND(2>1,4>8) would return FALSE
=AND(4>2,4<8) would return TRUE
=OR(1=2,3=4) would return FALSE
=OR(1=2,3=3) would return TRUE

So then the next part would be to ensure that your arguments return a boolean return. If you're looking at dates, this would return a boolean return...

=TODAY()=A2

That says today's date is equal to the value of A2. That is TRUE or it is FALSE, dependent on what value you have in A2. Make sense?

Another little tidbit of information when dealing with dates in Excel is that one whole day is expressed as one whole number. So half a day (12 hours) would be expressed as 0.5 in decimal form. Thus, to calculate hours, if... Read more

1 more replies
Relevance 58.22%

I am trting to create a chart using excel. I have inserted dates in column 1 - they are not sequential!. When I put the data into a chart it creates extra dates inbetween those I have actual placed in the cells. Help!
 

Answer:Solved: excel unwanted date columns

not a great solution - but format the dates as text - it should then only show the dates you have entered - and should be correct on the chart - but will be a number in the cells

not sure this is the correct way to go - but it seems to work

see attached
 

2 more replies
Relevance 58.22%

I'm trying to have a header display the last updated date on a spreadsheet. I know how to display the current date, but that might be different from the last update date, which would basically be the last date the spreadsheet was saved. Is there a way to do this? Thanks
 

Answer:Solved: excel: displaying last save date

9 more replies
Relevance 58.22%

I am using Excel 2003 windows XP Pro. I am having difficulty filerting the dates. I want to be able to sort different data using the mm/d/yyyy format. Is there a way to do this? Thanks
 

More replies
Relevance 58.22%

Hi everyone,

I'm very much a beginner and struggling with the following table regarding mid-year report dates .

PQRS1FROMTOMid-Year DueM-Y Complete201-Dec-1130-Nov-1231-May-12Y301-Nov-1131-Oct-1201-May-12Y401-Jul-1130-Jun-1330-Jun-12Y530-Jul-1230-Jun-1313-Jan-13N601-Jun-1231-May-1330-Nov-12N701-Feb-1231-Jan-1301-Aug-12Y

I am trying to get cells in column R to go: AMBER when it is within 1 month of the due date and RED when it is overdue unless there is a Y in column S, in which case it would go GREEN.

I have attempted conditional formatting and am getting nowhere.

I should be grateful if an Excel genius out there could point me in the right direction.
 

More replies
Relevance 58.22%

Hi everyone,

I'm very much a beginner and struggling with the following table regarding mid-year report dates on the attached worksheet.

I am trying to get cells in column R to go: AMBER when it is within 1 month of the due date and RED when it is overdue unless there is a Y in column S, in which case it would go GREEN.

I have attempted conditional formatting and am getting nowhere.

I should be grateful if an Excel genius out there could point me in the right direction.
 

Answer:Solved: Date Alert Excel 2003

I have attempted conditional formatting and am getting nowhere.Click to expand...

should do it
this will test for 31 days less
in one of the conditional format rules put in 3 rules
=if(S2="Y", true, false) make green

=IF(AND(TODAY()>C2, TODAY()-31<C2), TRUE,FALSE) make amber - within 1 month

=IF((TODAY()-31>C2), TRUE,FALSE) make red

see attached spreadsheet - tests Column C2
 

3 more replies
Relevance 58.22%

Hi,

for each client I have I am calculating a date that is three years in the future minus one day.
(i.e. 02/01/2011 gives an output of 01/01/2014)

I want the formula to check the client's date of birth in another cell and calculate a different date if the child is under 18. this date should be 21 years from the date of birth minus one day.

so excel is checking the date of birth of my client and performing a different calculation dependant on their age.

Please see attachment for an example.

any help would be greatly appreciated.
Regards,
Dan
 

Answer:Solved: Conditional Future Date in Excel

If you google for it yolu will find a lot of code to calcuate age.
I added the function AGE() to your file and included it your formual.
The sheet must be macro enabled to run.
 

2 more replies
Relevance 58.22%

I'm using Excel 2000 and XP. I'm doing form letters and trying to merge dates that are formatted "20020721". How can I change the format to 7/21/2002 when merged?
 

Answer:Solved: Excel 2000 date merge

11 more replies
Relevance 58.22%

So im using Excel 2010, and i making a timetable.

Right now the row going across saying period 1 2 3 they are all different sizes, some will be bigger and some would be smaller. I was wondering if there is a way to make them all the same size.


If you dont know what im talking about ask for a screenshot.

Im to busy to upload one now

Answer:Row Length in Excel

do you want the rows to all be the same height?

click on the first row number on the left - shift-click on the last one to select all the rows.

then right-click on the row numbers >> row height >> type in a number...

you can do a similar thing with the columns too.

is this what you meant?

4 more replies
Relevance 58.22%

Hi guys,

Something weird is happening. Some of my movie files (file type doesn't matter) became corrupted; when I play them in VLC they cause errors, they were just fine before and nothing has changed. I noticed in Explorer that these files have no Date Modified field, it's simply blank; I've never seen anything like that.

I did a chkdsk on the drive and see no errors. What could it be ?

Thanks.

Answer:Blank Date Modified Field ?

VLC should rebuild the Index, but that is a temporary fix. Depending on what format they are, there is "DIVFIX" and "Meteor MKV repair" you can try to run the faulty content through which of those is appropriate, you'll have to google it, cant remember the links.

3 more replies
Relevance 58.22%

hi, i searched in vain for an answer to this question:
when i edit an image file and resave, the date modified date does not change. since i started using computers 100s of years ago, this was automatic. how can i enable this (obvious) field? what point is there in even having a date modified field if it stays the same as the date created field? thanks!

Answer:date modification field does not change

Are you sure you have the Date Modified selected as a column to display?

Right click on the header column (has Name, Date, Type, etc.) and select the Date Modified tag.

9 more replies
Relevance 58.22%

Hello, I need help with the following:
I have a field (called Date_Time) which displays for example 1/31/2005 12:00:00 AM. I would like to run a query that converts that value to '200501' in a created field.
I tried the following below but I keep having problems.

select Account_Number, Date_Time,
concat(year(Date_Time), month(Date_Time)) as Date_Time_Modified
from table
where Account_Number = xxxx

Any insight into this would be greatly appreciated. Thank you.


 

Answer:Concatenating Date and Time Field in SQL

Just wanted to add some more examples:

Account_Number: Integer Type
Date_Time: Date Type
Date_Time: Modified: Character Type

Account_Number Date_Time Date_Time_Modified
3246 1/31/2005 12:00:00 AM 200501
4920 11/30/2008 12:00:00 AM 200811
5932 6/30/2011 12:00:00 AM 201106
... ... ....
 

1 more replies
Relevance 58.22%

Hello everyone,

I'm working on an order management database and I'm stuck on one item.

I have a form that users can fill in with purchase order data. One section of the form relates to contract review completion.

I would like to have a checkbox that the user can check when contract review is complete, but I'd also like for a completion date field to be auto-filled when the checkbox is checked.

I've tried a couple of things, both unsuccessful.

Any ideas?

Thanks.
 

Answer:MS Access - autofill date field

6 more replies
Relevance 58.22%

Is there a way to insert a date merge field where the date is spelled out (June 10, 2008) and will not change if the document is reopened, resaved, modified, etc?   Thanks for any help!

Answer:MS Word merge field for date

I'm not quite sure I understand the question, but depending on the Word document...you can use the "Insert" link from Word's toolbar to insert the date into a document...formatted to your liking.Once the document is saved...the date will always appear as you have set it.

2 more replies
Relevance 57.81%

I have a personnel tracker that tracks required documents that need to be renewed every year. There are 4 of these and each has a seperate column for each date. They are J,L,T and U.

I would like excel to scan each of these columns every time the document is opened for any of those dates that are 60-65 days from expiring, I use Now()-300 to check this.

But if the cell is blank I want it ignored, which right now it does not. If it finds 1 or even 80 that are in that range I want 1 email generated to a list of management personnel.

Currently it generates how ever many are blank or expiring. I use outlook.

My data starts on row 3 and goes down to 331, the required and maximum number of people allowed on the contract.

If I can get just one email with a generic message I would be happy. But if I could I would like the info that is actually doing the triggering to be copied and pasted in the email to include the info in A, C and D.

Here is my current code, I would change .display to .send once I have it working. please help.

Private Sub Workbook_Open()
Dim Cell As Range
Dim DateRng As Range
Dim Msg As String
Dim olApp As Object
Dim olEmail As Object
Dim RngEnd As Range
Dim Wks As Worksheet

Set Wks = Worksheets("Work Site Info")

Set DateRng = Wks.Range("J3")
Set RngEnd = Wks.Range("J331")
Set DateRng = IIf(RngEnd.Row < DateRng.Row, DateRng, Wks.Range(DateRng, RngEnd))

'Change this to what you want.
Msg = "This is message is the bo... Read more

Answer:Solved: Automatic email from excel based on date

16 more replies
Relevance 57.81%

Is there a way to write a formula that adds down the column 1 day to the previous day, but when it hits a Saturday or a Sunday, it goes to Monday?
ie: (a1+1) but by the time it got to April 3 it would know to add 3 to get to Monday
Monday, March 30, 2009
Tuesday, March 31, 2009
Wednesday, April 01, 2009
Thursday, April 02, 2009
Friday, April 03, 2009
Saturday, April 04, 2009
Sunday, April 05, 2009
Monday, April 06, 2009
Tuesday, April 07, 2009

Thanks for any information you can provide.

BL
 

Answer:Solved: excel date formula - ignore weekends

try:

=a1+IF(WEEKDAY(a1)=6,3,1)

If WEEDAY of previous cell = 6 (=Friday) then add 3 else add 1
 

3 more replies
Relevance 57.81%

Hi

I am trying to get a date value from one sheet to populate another sheet using the offset function.

Using:

Range(Selection, Selection.End(xlDown)).Offset(0, 3)= " (need to show date here from another sheet) "

Any help would be much appreciated..

Thanks
 

Answer:Solved: Using Offset in Excel VBA to Return date frm another cell

8 more replies
Relevance 57.81%

Hi,

Want a macro to sort data from (Column B,C,D,E) with whatever date mentioned in Cell A2.
Column B,C,D,E have different dates what I want is to sort those columns as per the date mentioned in cell A2.
Suppose if in cell A2 the date is 11/26/2009 then it should sort the data from the Column B,C,D,E with that particular date in a new sheet in that workbook.
And would like to assign that particular macro to a button

Please help
 

Answer:Solved: Sort today date with Macro in excel

16 more replies
Relevance 57.81%

Does anyone know how to change the format of the [date] that is put into a footer?

Thanks!
 

Answer:Solved: Excel - Changing date format in footer

Hi slgraham22,

the only way is via a macro: http://support.microsoft.com/?kbid=213742

lol
Hew
 

3 more replies
Relevance 57.81%

Hello.
I have been asked to send warning e-mails to a short list of adresses from an Excel file where reminder dates correspond to the actual date, and I have no clue how to do this with Macro’s. I’v tryed to understand how some examples of macros dealing with Excel and e-mails with no success as I still have a lot to learn…

Here is my problem : I have a spreadshhet containing company names (col.A) and contract renewal dates (col.D). there are about 80 companies listed. When the actual date corresponds to the reminder date an e-mail containing the company name, a fixed blabla and the reminder date should be send to 3 known e-mail adresses.
Could this be done with an Excel Macro
I’m using Windows 7 32bits, Office 2010 with Outlook as mailing system.

Many thanks in advance.
Robert
 

Answer:Solved: Reminder date in Excel sends an e-mail

16 more replies
Relevance 57.81%

Basicly all I want to do is get a count of entries in a list, if the date in the cell is less than today. The formula I have been trying to use is =COUNTIF(O73,(O73<TODAY())) but its not working so I am obviously doing something wrong can anybody help ?

I have a workround by using a hidden column and a few other arguments etc. but its cumbersome I like the elegance of a simple formula and it should be easy !
 

Answer:Solved: Excel 2003 - COUNTIF() Date Comparison

The syntax for countif is =Countif(range,test)

Range should be a range of cells, your formula will at most return 1 if O73 < today.
If you want to count all the ones between O1 and O100 you'd put O1:O100 in the place of range.

Test works on the cell being inspected automatically so you don't need to repeat the cell. What you do need is quote marks (not parentheses) around the criteria. For example to count the numbers in O1 to O100 whose value is less than 4 you'd write =countif(O1:O100,"<4")

Because Today() is itself a function it doesn't go within the quotes. It is joined to the less than sign by an ampersand. So in your particular case you'd want

=COUNTIF(O1:O100,"<"&TODAY())
 

2 more replies
Relevance 57.81%

I am trying to build an excel sheet to tell me my future manning levels, (for example, how many workers will I have 6 months from now). I attached (or at least tried to) a very small scale "example sheet" of what I am talking about. It basically will have one cell to put a start date in for which month to start gathering data. This is followed by the months following the start date going out a year. The data comes from a separate sheet and the data is based off of a column with dates that I am suppose to receive inbound and a column with dates of personnel departing. I am trying to take the month that I need the data for, count the personnel that should have arrived by that date and add it to those that have not departed by that date. Every formula that I have tried counts every cell to include the empty ones. This is the last formula that I tried =SUM(IF(Manning!$C$3:$C$14<=H3,1,0))+SUM(IF(Manning!$D$3:$D$14>=H3,1,0)). I have also tried COUNTIF formulas. How do I get it to count only the cells that have a date in it that falls with in the range? Sorry if it is confusing but hopefully the attached sheet will help explain. I am using an XP machine.
 

Answer:Solved: Using excel to count a specific date range

I put in a check to put in a ZERO if there is an empty cell.

=SUM(IF(Manning!$D$3:$D$14<=H3,IF(Manning!$D$3:$D$14="",0,1),0))+SUM(IF(Manning!$F$3:$F$14>=H3,IF(Manning!$F$3:$F$14="",0,1),0))
 

3 more replies
Relevance 57.81%

Hi everyone, I was having some trouble with the format of the date in my excel document.

Basically what i want to do is type in for example todays date (Aug 18), and then when clicking below onto the next row, I would like to see the date come up above as (08/18/2005).

Instead the problem im getting is the date comes up as (08/18/2018)????

Ive tried right-clicking the entire column, where the date is being entered, and using "Format Cells" to set the appropriate date format. I have also verifed the date is set at 2005, so far nothing works.

I welcome any ideas/suggestions???

Thanx everyone
 

Answer:Solved: Trouble formatting date in excel spreadsheet

10 more replies
Relevance 57.81%

Not very fluent in Excel. I am setting up a positive pay format for my company to transfer check information from my PC to a bank via an ASCII Flat-File. Question is, I have so many columns that need to be set at 10 characters fixed length.

Is there way for this to be done? Example if I key in a check for $1.95

I want it to automatically put it as 0000000195.

Can this be done?

Thanks
 

Answer:Fixed Length in Excel

6 more replies