When copying from "date" cell to "general" cell, how to keep date.

An Excel question for you:  when I try to copy data from a "date" formatted 
cell over to a concatenate formula in a "general" formatted field, it 
transfers the data over as the boolean value.  This is causing endless 
headaches because I am trying to upload the information into our financial 
software.  Do you know a solution for this?

0
Aspen (2)
9/17/2004 1:25:14 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
716 Views

Similar Articles

[PageSpeed] 42

I think you mean the date comes over as the serial value. 
If not, this may not be right.  In the concatenate 
function, embed the Text function. ex: A1 has 5/16/2004, 
B1 has "TEST" in it.  C1 formula would be =concatenate
(TEXT(A1,"mm/dd/yy"),";",B1).  The result would be 
5/16/2004;TEST.  

>-----Original Message-----
>An Excel question for you:  when I try to copy data from 
a "date" formatted 
>cell over to a concatenate formula in a "general" 
formatted field, it 
>transfers the data over as the boolean value.  This is 
causing endless 
>headaches because I am trying to upload the information 
into our financial 
>software.  Do you know a solution for this?
>
>.
>
0
anonymous (74717)
9/17/2004 1:47:38 PM
WOW!  Thanks for the quick response and excellent solution!  You solved a 
problem I have been dealing with for months.  I greatly appreciate it!

"Peg" wrote:

> I think you mean the date comes over as the serial value. 
> If not, this may not be right.  In the concatenate 
> function, embed the Text function. ex: A1 has 5/16/2004, 
> B1 has "TEST" in it.  C1 formula would be =concatenate
> (TEXT(A1,"mm/dd/yy"),";",B1).  The result would be 
> 5/16/2004;TEST.  
> 
> >-----Original Message-----
> >An Excel question for you:  when I try to copy data from 
> a "date" formatted 
> >cell over to a concatenate formula in a "general" 
> formatted field, it 
> >transfers the data over as the boolean value.  This is 
> causing endless 
> >headaches because I am trying to upload the information 
> into our financial 
> >software.  Do you know a solution for this?
> >
> >.
> >
> 
0
Utf
9/17/2004 2:15:05 PM
Reply:

Similar Artilces:

Convert general format to date
I recently scanned a number of newspaper articles and saved them as PDF files using this as filename: article_newspaperddmmyy e.g. Big story_FreePress050206 where 050206 refers to February 5, 2006. I have a folder with over 300 filenames like that. I used a PrintFolder program to list these filenames and export them into Excel. By using the Text to Columns feature I was able to place the date reference part of the filename into a separate column. However I notice that after doing this I have a problem example 050206 appears as 50206, the leading zero disappears. How can I convert this Gene...

Writing a General Date from separate date and time fields
Hi all, I have records which need to be modified, to allow for "after-the- fact" entries. What I am trying to achieve is combining a date field (formatted as Medium Date) and a time field (formatted Short Time) from a form entry, and then updating a field in the table which is formatted as General Date. It will normally be to give the correct timestamp for the start of a phone call, even if the database entry is made two hours later. The purpose? So I can correctly calculate the difference between two General Dates, [CallDate] and [CallEnd], (and the associated tim...

How to change date as general "200306" to date "06/2003"
I recieve data with a date formated as general that looks like "200306". I need to change it to a date format that looks like this "06/2003" How do I do this =DATE(LEFT(A2, 4),RIGHT(A2,2),1) This will actually return a date for Jun 1, 2003, which you can format for "06/2003" with Format - Cells - Number - Custom: mm/yyyy. Or if you don't want a for-real Excel date, you can just convert the text string: =RIGHT(A2,2) & "/" & LEFT(A2,4) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------...

Input Mask for General Date/Time
Team Thank you in advance for helping me with my problem. I have used this forum to build a form to use as a switchboard for customers. In the forms OnLoad event, I want to compare a date and time field I have entered on a table against Now() to see if the customer can still access the "switchboard" I have a form called frmDates where I can add the "Stop" info I would like to add a input/edit mask to the stop field so that it is correct. example of data that would be entered in form 12/27/2009 5:00:00 PM Table is called tblDates containing StartDate (...

MSExchangeIS ( 9554
I get a repeat of 14 Guid's daily in the application log with the following text: Event ID: 9554 Unable to update Mailbox SD in the DS. Mailbox Guid: 3be863ed-db68-4676-ac5f-1a7a19277be1. Error Code 0x8004010f For more information, click http://www.microsoft.com/contentredirect.asp. I try to identify the Guid by running the ADFind utility and it will not resolve back to anything. Is there something within Exchange that I can use to see what mailbox is tagged with what GUID? I can't use ADSI edit becuase I have no idea what actual AD user this GUID is attached to. The only ...

Date Changes to General
We have worksheets with dates in them. They are formatted at Month and year (Nov-09 mmm-yy for 11/16/2009. Sometimes when I open the sheets the formatting is lost and the dates are show as general numbers 40134 in this case. Any idea why this happends? We share workbooks in the office - could it be that custom formats are not stored witth the file? any ideas of how to fix this annoying problem would be helpful. Thanks Formatting is stored with the file. Could it be that someone is inadvertently changing the format of those cells? HTH Otto "Michael" <mfgtcb@gma...

How do I stop a general format from being autoformatted to date
I want to stop Excel from automatically changing the format of a cell from general to time every time I enter a new row of data. I have several column headings running across the top of the page and I have set the format of 4 columns to display the time (HH:MM), the next column has been formatted to show a GENERAL entry. This was done by selecting the whole column and applying the format of General. The problem is when I enter data for a particular row I enter from left to right and put the data in the 4 TIME formatted cells first, by the time I go to the 5th cell which should just be a n...

convert 13.11.2009 general to 13/11/2009 date, how to
Dear all, Office 2003, Windows XP Pro SP3 I have a column of dates in the following form 13.11.2009 (i.e. dd/mm/yyyy) where the cells' format are 'General'. I wanted to sort in ascending date order from 01/01/2009 to 31/12/2009. I tried formatting the cells to date and use the sort but what that did was to sort in this manner: 01.01.2009, 01.02.2009 - - - 01.12.2009 then 02.01.2009, 02.02.2009 i.e. by months first then by dates. Even when I have chosen dd/mm/yyyy formatting, not only the content won't change the sort still perform as I have just described. I h...

Change general format to US date format
I have a list of contacts with data. One of the columns is for "date". However, ONe of the lists I have has the date formatted as "general" an when I try to format it as date it puts the wrong number in it. Example: column G reads 10298 which is the date 01/02/1998. N matter what I try I cannot get it to read as a date. Please help. have searched the forum over and over and cannot find the answer t this issue. My anxiety is through the roof! email help to woodlot4 at yahoo.co -- woodlot ----------------------------------------------------------------------- wood...

Posting date in general ledger
I would like to have a warning of a post date for manual entries in the general ledger module. It automatic defaults to the system date and I have posted to future months in error. Ex. I am doing adjusting journal entries for May in June and it posted to June. Since you cannot completely delete entry, my june general ledger has a entries that should not be their (along with the voids). Messy looking. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" b...

When copying from "date" cell to "general" cell, how to keep date.
An Excel question for you: when I try to copy data from a "date" formatted cell over to a concatenate formula in a "general" formatted field, it transfers the data over as the boolean value. This is causing endless headaches because I am trying to upload the information into our financial software. Do you know a solution for this? I think you mean the date comes over as the serial value. If not, this may not be right. In the concatenate function, embed the Text function. ex: A1 has 5/16/2004, B1 has "TEST" in it. C1 formula would be =concatenate (TEX...

How to convert General date in julian date
Hi. In excel if I type into a cell today's date 26-05-2010 and then if I format the cell as general, the value will be like this: 26-05-2010 » 40324 How can I do this in access? Regards, Marco If the date is in an actual date/time field, you could display is like you wish in a query, form, or report with somehing like: CLng([FieldName]) However you are going to run into problems with the DD-MM-YYYY date format. debug.Print CLng(#13-05-2010#) = 40311 (13-May-2010) debug.Print CLng(#12-05-2010#) = 40517 ( 5-Dec-2010) Access assumes MM-DD-YYYY first and r...

GP 9.0 General Ledger Posting date is missing or invalid
I'm hoping this is an easy fix since I can't find any comments on this error. When I attempt to post Sales transaction batches, no matter how I have configured the system I always receive an error in the posting report stating **ERROR: The General Ledger posting date is missing or invalid. Does anyone have any advice on how to get around this? Thanks! Louis Delouiser Louis, I have seen this happen when you have existing transactions and change you posting setup options. Try this as a test: open one of the transactions that is getting this error, move it to a new batch...

convert general to date
I have imported a text file where all fields were character and came in as general. They look like: Nov 17 2009 9:39AM I want to compute time differences between columns, but I can't get them converted to Date/Time format. Thanks very much for any ideas. This worked for me with data that looked like that one sample you shared. I selected the offending column (one column at a time) Edit|replace what: AM (two characters) with: _AM (space character, AM (3 characters)) replace all Then I made sure that there were two empty columns to the right of this offending columns...

Using Between...And with General Date
I am trying to query all records created between given dates. The field, DtRecCrt, is a general date field that is set to =Now() whenever the records are created. However, when I run the query and have the criteria for DtRecCrt set to "between [start date] and [end date]," it will return records up to but not including the end date specified. How do I make the query include records created on the end date? Thanks On Fri, 30 Apr 2010 13:08:04 -0700, mewins wrote: > I am trying to query all records created between given dates. The field, > DtRecCrt, is a gen...

General Date in query
Can you not set an open criteria in a query for a field set to a General Date? When I run my query with no criteria, entries are returned. When I put [Enter Date] in the Criteria nothing is returned. I have checked the date to make sure it was just not my error, but the date is correct. I have also tried Like "*" &[Enter Date]& "*" This did not work either. Any thoughts of what I am doing wrong or if I cannot search this way? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1 POST your SQL. (View: S...

formating date from general2 custom
I have an input xls file with the date but like this: Wed May 14 00:00:00 GMT-05:00 2008 I need to have just following format dd/mm/yyyy I tried to format it using Format->Cells->Date or Format->Cells- >Custom but this is failing. Nothing changed. Any ideas? Thanks for the help. Vlad <vladimir.vaynshtok@gmail.com> wrote in message news:1013b138-352b-4799-81ba-9508b74a1850@t54g2000hsg.googlegroups.com... >I have an input xls file with the date but like this: > > Wed May 14 00:00:00 GMT-05:00 2008 > > I need to have just following format > > d...

Modify general format to date format.
When importing from payroll, it gives me all dates formatted as general numbers (i.e. 07/17/2009 is presented as 7172009. Is there any way to convert these numbers into dates? If your Windows Regional Settings recognise a format of mddyyyy, you can apply the below formula and format the formula cell to date.. =--TEXT(A1,"00\-00\-0000") -- Jacob (MVP - Excel) "SherryS" wrote: > When importing from payroll, it gives me all dates formatted as general > numbers (i.e. 07/17/2009 is presented as 7172009. Is there any way to > convert the...

General number formats changing to Dates or Euros
What causes Excel 2007 to change general number formats from general to dates or euros? How can I easily fix a big model that has lots of this occuring all over it? Thanks, Ken You must be very careful about entering data. Even if a cell is formatted general, entering something like 1/1 will cause to "re-format" as a Date. -- Gary''s Student - gsnu200908 "KMH" wrote: > What causes Excel 2007 to change general number formats from general to dates > or euros? How can I easily fix a big model that has lots of this occuring > all o...

column formatted to general
In my spreadsheet I have a particular column that I have formatted to general but anytime I enter numbers into a cell in that column, it automatically changes it to a date and I have to re-format that cell to general. What type of numbers? Like 1-12 or similar? Pre-format the cells as Text or preface the numbers with an apostrophe. Gord Dibben MS Excel MVP On Thu, 11 Sep 2008 08:45:08 -0700, lilsparkdog <lilsparkdog@discussions.microsoft.com> wrote: >In my spreadsheet I have a particular column that I have formatted to general >but anytime I enter numbers into a cell in...

General inventory Update
My company sells books at trade-shows and sell books on site as well as shipped from our warehouse. Is there anyway to do a massive inventory update for all items to be set to one number? If not what will happen when the inventory hits 0, will RMS not allow us to sell any more of that title? RMS will not block the sale of any item unless you have it set in the configuration to do so. You will just get a dialog box saying you have 0 in stock, and then it will ask you whether to accept it or not. You can also set it up in config to give you the option to backorder the item. Craig &quo...