| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date-Mid Function
I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2))
and the cell it is referencing has this in it: 0870526 (with the cell format set as custom as '0000000' The result it is producing is: 4/6/2774 It should be 5/26/1987. Does anyone know what is going wrong? -Anthony Morano Pension Intern |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
If you have G7 formatted as text (or started the entry with an apostrophe), then
your formula works ok. But if G7 is a real number with a custom format to show that leading 0, then the value in the cell is really just: 870526 And that screws up your =mid() pieces. wrote: I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2)) and the cell it is referencing has this in it: 0870526 (with the cell format set as custom as '0000000' The result it is producing is: 4/6/2774 It should be 5/26/1987. Does anyone know what is going wrong? -Anthony Morano Pension Intern -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
Dave's exactly right. The formatting is only changing the DISPLAY of what's
in G7, not the cell contents which you're referencing in your formula. You could adjust your formula by changing each G7 to be text(G7,"0000000"). Or, if the value in G1 is ALWAYS six digits, just adjust your arguments to the mid function appropriately: =date(1900+mid(g7,1,2),mid(g7,3,2),mid(g7,5,2)) "Dave Peterson" wrote: If you have G7 formatted as text (or started the entry with an apostrophe), then your formula works ok. But if G7 is a real number with a custom format to show that leading 0, then the value in the cell is really just: 870526 And that screws up your =mid() pieces. wrote: I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2)) and the cell it is referencing has this in it: 0870526 (with the cell format set as custom as '0000000' The result it is producing is: 4/6/2774 It should be 5/26/1987. Does anyone know what is going wrong? -Anthony Morano Pension Intern -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
As you have a number, not text, use Int and Mod, rather than Mid, as in:
=DATE(1900+INT(G7/10000),MOD(INT(G7/100),100),MOD(G7,100)) Regards, Fred. wrote in message ... I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2)) and the cell it is referencing has this in it: 0870526 (with the cell format set as custom as '0000000' The result it is producing is: 4/6/2774 It should be 5/26/1987. Does anyone know what is going wrong? -Anthony Morano Pension Intern |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
Ok- that's what I thought about the display only being changed. The
problem I'm going to run into is that I have about 28,000 records where some value have a 0 and some have a 1. I don't run into the problem where there is a 1 in the beginning- the 1 represents the century. How should I go about formatting these? -Anthony Morano |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
I haven't checked it but if the date is 000101 it will probably be 101 in
the cell and the formula will fail -- Regards, Peo Sjoblom "Fred Smith" wrote in message ... As you have a number, not text, use Int and Mod, rather than Mid, as in: =DATE(1900+INT(G7/10000),MOD(INT(G7/100),100),MOD(G7,100)) Regards, Fred. wrote in message ... I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2)) and the cell it is referencing has this in it: 0870526 (with the cell format set as custom as '0000000' The result it is producing is: 4/6/2774 It should be 5/26/1987. Does anyone know what is going wrong? -Anthony Morano Pension Intern |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
Try this formula...
=DATE(1900+MID(G7,1,LEN(G7)-4),LEFT(RIGHT(G7,4),2),--RIGHT(G7,2)) Rick wrote in message ... Ok- that's what I thought about the display only being changed. The problem I'm going to run into is that I have about 28,000 records where some value have a 0 and some have a 1. I don't run into the problem where there is a 1 in the beginning- the 1 represents the century. How should I go about formatting these? -Anthony Morano |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
Not sure if this applies to all your data, but...
[A1] = 870526 =DATEVALUE(TEXT(A1+19000000,"####\/##\/##")) ..and format the cell to show 5/26/1987 -- HTH :) Dana DeLouis wrote in message ... I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2)) and the cell it is referencing has this in it: 0870526 (with the cell format set as custom as '0000000' The result it is producing is: 4/6/2774 It should be 5/26/1987. Does anyone know what is going wrong? -Anthony Morano Pension Intern |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
I would suggest changing the forced slashes to dashes instead. I'm not sure
if Excel will use regional settings for the day/month order when slashed dates with the year first are used, but I'm sure using dashes forces the year-month-day interpretation no matter what the regional setting.... =DATEVALUE(TEXT(G7+19000000,"####-##-##")) Rick "Dana DeLouis" wrote in message ... Not sure if this applies to all your data, but... [A1] = 870526 =DATEVALUE(TEXT(A1+19000000,"####\/##\/##")) ..and format the cell to show 5/26/1987 -- HTH :) Dana DeLouis wrote in message ... I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2)) and the cell it is referencing has this in it: 0870526 (with the cell format set as custom as '0000000' The result it is producing is: 4/6/2774 It should be 5/26/1987. Does anyone know what is going wrong? -Anthony Morano Pension Intern |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
Rick- that formula worked great!!!!.... Thanks I really appreciate it.
Thanks to everyone for their input also. -Anthony |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
DATEVALUE is a totally obsolete function unless one uses it for pedagogical
reasons (to show one wants a date) =--TEXT(G7+19000000,"####-##-##") -- Regards, Peo Sjoblom "Rick Rothstein (MVP - VB)" wrote in message ... I would suggest changing the forced slashes to dashes instead. I'm not sure if Excel will use regional settings for the day/month order when slashed dates with the year first are used, but I'm sure using dashes forces the year-month-day interpretation no matter what the regional setting.... =DATEVALUE(TEXT(G7+19000000,"####-##-##")) Rick "Dana DeLouis" wrote in message ... Not sure if this applies to all your data, but... [A1] = 870526 =DATEVALUE(TEXT(A1+19000000,"####\/##\/##")) ..and format the cell to show 5/26/1987 -- HTH :) Dana DeLouis wrote in message ... I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2)) and the cell it is referencing has this in it: 0870526 (with the cell format set as custom as '0000000' The result it is producing is: 4/6/2774 It should be 5/26/1987. Does anyone know what is going wrong? -Anthony Morano Pension Intern |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
I was concentrating so much on the arguments to the TEXT function that I
didn't even pay attention to the unnecessary DATEVALUE function it was embedded in. Thanks for picking up on that. Rick "Peo Sjoblom" wrote in message ... DATEVALUE is a totally obsolete function unless one uses it for pedagogical reasons (to show one wants a date) =--TEXT(G7+19000000,"####-##-##") -- Regards, Peo Sjoblom "Rick Rothstein (MVP - VB)" wrote in message ... I would suggest changing the forced slashes to dashes instead. I'm not sure if Excel will use regional settings for the day/month order when slashed dates with the year first are used, but I'm sure using dashes forces the year-month-day interpretation no matter what the regional setting.... =DATEVALUE(TEXT(G7+19000000,"####-##-##")) Rick "Dana DeLouis" wrote in message ... Not sure if this applies to all your data, but... [A1] = 870526 =DATEVALUE(TEXT(A1+19000000,"####\/##\/##")) ..and format the cell to show 5/26/1987 -- HTH :) Dana DeLouis wrote in message ... I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2)) and the cell it is referencing has this in it: 0870526 (with the cell format set as custom as '0000000' The result it is producing is: 4/6/2774 It should be 5/26/1987. Does anyone know what is going wrong? -Anthony Morano Pension Intern |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date-Mid Function
=--TEXT(G7+19000000,"####-##-##")
Didn't think of using the dash -, or --Text() Thank you. :) -- Dana DeLouis "Peo Sjoblom" wrote in message ... DATEVALUE is a totally obsolete function unless one uses it for pedagogical reasons (to show one wants a date) =--TEXT(G7+19000000,"####-##-##") -- Regards, Peo Sjoblom "Rick Rothstein (MVP - VB)" wrote in message ... I would suggest changing the forced slashes to dashes instead. I'm not sure if Excel will use regional settings for the day/month order when slashed dates with the year first are used, but I'm sure using dashes forces the year-month-day interpretation no matter what the regional setting.... =DATEVALUE(TEXT(G7+19000000,"####-##-##")) Rick "Dana DeLouis" wrote in message ... Not sure if this applies to all your data, but... [A1] = 870526 =DATEVALUE(TEXT(A1+19000000,"####\/##\/##")) ..and format the cell to show 5/26/1987 -- HTH :) Dana DeLouis wrote in message ... I have this formula: =DATE(1900+MID(G7,1,3),MID(G7,4,2),MID(G7,6,2)) and the cell it is referencing has this in it: 0870526 (with the cell format set as custom as '0000000' The result it is producing is: 4/6/2774 It should be 5/26/1987. Does anyone know what is going wrong? -Anthony Morano Pension Intern |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date function in ACCRINTM requires date format not available | Excel Worksheet Functions | |||
| MAX figure within a date range as a function of today()'s date | Excel Worksheet Functions | |||
| SUMIF within date range as a function of today()'s date | Excel Worksheet Functions | |||
| Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
Linear Mode
