Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why are my dates show up as (example) 39538 in formula?
I have been racking my brain to figure out why my formula is not working but
now I see that when reviewing the results through the functional argument tool I see that excel is reading the day (march 31 2010) as 39538. On the spreadsheet it clearly shows me the correct date format but when excel is reading the formula it is not. Can someone help? Thanks -- Neall |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why are my dates show up as (example) 39538 in formula?
Neall;373162 Wrote: I have been racking my brain to figure out why my formula is not working but now I see that when reviewing the results through the functional argument tool I see that excel is reading the day (march 31 2010) as 39538. On the spreadsheet it clearly shows me the correct date format but when excel is reading the formula it is not. Can someone help? Thanks -- NeallWhat formula are you using? you might have to use a DATE function...supply the formula and we can help you with that. -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104449 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why are my dates show up as (example) 39538 in formula?
Hi Neall
The below is a link by Chip on excel date/times...Once you go through post back incase you have any issues in working with formulas with date... http://www.cpearson.com/excel/datetime.htm If this post helps click Yes --------------- Jacob Skaria "Neall" wrote: I have been racking my brain to figure out why my formula is not working but now I see that when reviewing the results through the functional argument tool I see that excel is reading the day (march 31 2010) as 39538. On the spreadsheet it clearly shows me the correct date format but when excel is reading the formula it is not. Can someone help? Thanks -- Neall |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why are my dates show up as (example) 39538 in formula?
Hi,
Dates in Excel are numbers - format one as general to see that - and we only see them as dates because of the applied formatting. The problem your experiencing arises because in a formula that format can be lost and has the be added back by formatting the formula cell as a date or using the TEXT function. If you post the problematic formula then someone will help you sort it out. Mike "Neall" wrote: I have been racking my brain to figure out why my formula is not working but now I see that when reviewing the results through the functional argument tool I see that excel is reading the day (march 31 2010) as 39538. On the spreadsheet it clearly shows me the correct date format but when excel is reading the formula it is not. Can someone help? Thanks -- Neall |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why are my dates show up as (example) 39538 in formula?
First, I'm confused.
39538 is either March 31, 2008 when using 1900 as excel's date system or April 1, 2012 when using 1904 as the date system. This can be toggled via: tools|Options|Calculation tab|Check or uncheck 1904 date system (in xl2003 menus) But dates are just numbers to excel. So if you have March 31, 2010 in A1 and =A1 in another cell (say B1), then make sure B1 is formatted as a date. If you see that 39538 in the cell, but the date in the formula bar, then you're looking at formulas: Tools|Options|view tab|uncheck formulas (xl2003 menus) or use the shortcut in any version ctrl-` (ctrl-backquote, the key to the left of the 1/! on my USA keyboard) =========== Saved from a previous post: Be aware that any existing date will now be off by 4 years and 1 day. And copying dates between workbooks becomes a problem, too. One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Neall wrote: I have been racking my brain to figure out why my formula is not working but now I see that when reviewing the results through the functional argument tool I see that excel is reading the day (march 31 2010) as 39538. On the spreadsheet it clearly shows me the correct date format but when excel is reading the formula it is not. Can someone help? Thanks -- Neall -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why are my dates show up as (example) 39538 in formula?
Thanks everyone
So, I have a column of dates that are formatted and showing ex:2008 when formatted by date however, if I look at the same date fields formatted as "normal" I see 39538 I am trying to get a sum of all paid part numbers by year (2007,2008,2009,2010) which is this =SUMIF(A8:M23,K24,L8:L23) A8: M23 is the entire table K24 - 27 are the date variable (2007,2008,2009,2010) L8 - L23 are the prices A 8 - A 23 are the dates columns Basically in this table, show me the sum of part numbers purchased for each year. Hope you can help -- Neall "Mike H" wrote: Hi, Dates in Excel are numbers - format one as general to see that - and we only see them as dates because of the applied formatting. The problem your experiencing arises because in a formula that format can be lost and has the be added back by formatting the formula cell as a date or using the TEXT function. If you post the problematic formula then someone will help you sort it out. Mike "Neall" wrote: I have been racking my brain to figure out why my formula is not working but now I see that when reviewing the results through the functional argument tool I see that excel is reading the day (march 31 2010) as 39538. On the spreadsheet it clearly shows me the correct date format but when excel is reading the formula it is not. Can someone help? Thanks -- Neall |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why are my dates show up as (example) 39538 in formula?
"Neall" wrote:
A8: M23 is the entire table K24 - 27 are the date variable (2007,2008,2009,2010) L8 - L23 are the prices A 8 - A 23 are the dates columns Basically in this table, show me the sum of part numbers purchased for each year. I presume you mean you want the sum of the prices. =sumproduct((year(A8:A23)=K24)*(L8:L23)) ----- original message ----- "Neall" wrote in message ... Thanks everyone So, I have a column of dates that are formatted and showing ex:2008 when formatted by date however, if I look at the same date fields formatted as "normal" I see 39538 I am trying to get a sum of all paid part numbers by year (2007,2008,2009,2010) which is this =SUMIF(A8:M23,K24,L8:L23) A8: M23 is the entire table K24 - 27 are the date variable (2007,2008,2009,2010) L8 - L23 are the prices A 8 - A 23 are the dates columns Basically in this table, show me the sum of part numbers purchased for each year. Hope you can help -- Neall "Mike H" wrote: Hi, Dates in Excel are numbers - format one as general to see that - and we only see them as dates because of the applied formatting. The problem your experiencing arises because in a formula that format can be lost and has the be added back by formatting the formula cell as a date or using the TEXT function. If you post the problematic formula then someone will help you sort it out. Mike "Neall" wrote: I have been racking my brain to figure out why my formula is not working but now I see that when reviewing the results through the functional argument tool I see that excel is reading the day (march 31 2010) as 39538. On the spreadsheet it clearly shows me the correct date format but when excel is reading the formula it is not. Can someone help? Thanks -- Neall |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why are my dates show up as (example) 39538 in formula?
Thanks for the suggestions I checked and 1904 date system was unchecked, I
made sure that I am not viewing the formulas, I even added another column and did a =A1 to see what I would come back with and the correct date ex: 3/31/3010 was displayed. Yet still in the formula tool I am seeing the numeric form ex. 39538. I may have missed a key point here, in the A column I am pulling the data from a DB sheet by using this formula =IF(ISERROR(INDEX(ALL,SMALL(IF(customername=$B3,RO W(customername)),ROW(RawData!1:1)),2)),"",(INDEX(A LL,SMALL(IF(customername=$B3,ROW(customername)),RO W(RawData!1:1)),2))) I am noticing in the main DB sheet that the date properties are ex: 39538 so when excel is pulling the data from the main DB its converting it. Any other suggestions? -- Neall "Dave Peterson" wrote: First, I'm confused. 39538 is either March 31, 2008 when using 1900 as excel's date system or April 1, 2012 when using 1904 as the date system. This can be toggled via: tools|Options|Calculation tab|Check or uncheck 1904 date system (in xl2003 menus) But dates are just numbers to excel. So if you have March 31, 2010 in A1 and =A1 in another cell (say B1), then make sure B1 is formatted as a date. If you see that 39538 in the cell, but the date in the formula bar, then you're looking at formulas: Tools|Options|view tab|uncheck formulas (xl2003 menus) or use the shortcut in any version ctrl-` (ctrl-backquote, the key to the left of the 1/! on my USA keyboard) =========== Saved from a previous post: Be aware that any existing date will now be off by 4 years and 1 day. And copying dates between workbooks becomes a problem, too. One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Neall wrote: I have been racking my brain to figure out why my formula is not working but now I see that when reviewing the results through the functional argument tool I see that excel is reading the day (march 31 2010) as 39538. On the spreadsheet it clearly shows me the correct date format but when excel is reading the formula it is not. Can someone help? Thanks -- Neall -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why are my dates show up as (example) 39538 in formula?
Format the cell with the formula as a date.
Formulas don't bring back formatting. You'll have to do it yourself. But your dates are still not going to be what you've shared. Neall wrote: Thanks for the suggestions I checked and 1904 date system was unchecked, I made sure that I am not viewing the formulas, I even added another column and did a =A1 to see what I would come back with and the correct date ex: 3/31/3010 was displayed. Yet still in the formula tool I am seeing the numeric form ex. 39538. I may have missed a key point here, in the A column I am pulling the data from a DB sheet by using this formula =IF(ISERROR(INDEX(ALL,SMALL(IF(customername=$B3,RO W(customername)),ROW(RawData!1:1)),2)),"",(INDEX(A LL,SMALL(IF(customername=$B3,ROW(customername)),RO W(RawData!1:1)),2))) I am noticing in the main DB sheet that the date properties are ex: 39538 so when excel is pulling the data from the main DB its converting it. Any other suggestions? -- Neall "Dave Peterson" wrote: First, I'm confused. 39538 is either March 31, 2008 when using 1900 as excel's date system or April 1, 2012 when using 1904 as the date system. This can be toggled via: tools|Options|Calculation tab|Check or uncheck 1904 date system (in xl2003 menus) But dates are just numbers to excel. So if you have March 31, 2010 in A1 and =A1 in another cell (say B1), then make sure B1 is formatted as a date. If you see that 39538 in the cell, but the date in the formula bar, then you're looking at formulas: Tools|Options|view tab|uncheck formulas (xl2003 menus) or use the shortcut in any version ctrl-` (ctrl-backquote, the key to the left of the 1/! on my USA keyboard) =========== Saved from a previous post: Be aware that any existing date will now be off by 4 years and 1 day. And copying dates between workbooks becomes a problem, too. One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Neall wrote: I have been racking my brain to figure out why my formula is not working but now I see that when reviewing the results through the functional argument tool I see that excel is reading the day (march 31 2010) as 39538. On the spreadsheet it clearly shows me the correct date format but when excel is reading the formula it is not. Can someone help? Thanks -- Neall -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why are my dates show up as (example) 39538 in formula?
Is there anyway I can send you screen captures of what I am seeing.
All date fields are showing the correct date format or what I need to get my formula to work, but when I review the information through the function Arguments window where I select the range I see the 39538 date format which is not the format I have selected or see in the actual spreadsheet. -- Neall "Dave Peterson" wrote: Format the cell with the formula as a date. Formulas don't bring back formatting. You'll have to do it yourself. But your dates are still not going to be what you've shared. Neall wrote: Thanks for the suggestions I checked and 1904 date system was unchecked, I made sure that I am not viewing the formulas, I even added another column and did a =A1 to see what I would come back with and the correct date ex: 3/31/3010 was displayed. Yet still in the formula tool I am seeing the numeric form ex. 39538. I may have missed a key point here, in the A column I am pulling the data from a DB sheet by using this formula =IF(ISERROR(INDEX(ALL,SMALL(IF(customername=$B3,RO W(customername)),ROW(RawData!1:1)),2)),"",(INDEX(A LL,SMALL(IF(customername=$B3,ROW(customername)),RO W(RawData!1:1)),2))) I am noticing in the main DB sheet that the date properties are ex: 39538 so when excel is pulling the data from the main DB its converting it. Any other suggestions? -- Neall "Dave Peterson" wrote: First, I'm confused. 39538 is either March 31, 2008 when using 1900 as excel's date system or April 1, 2012 when using 1904 as the date system. This can be toggled via: tools|Options|Calculation tab|Check or uncheck 1904 date system (in xl2003 menus) But dates are just numbers to excel. So if you have March 31, 2010 in A1 and =A1 in another cell (say B1), then make sure B1 is formatted as a date. If you see that 39538 in the cell, but the date in the formula bar, then you're looking at formulas: Tools|Options|view tab|uncheck formulas (xl2003 menus) or use the shortcut in any version ctrl-` (ctrl-backquote, the key to the left of the 1/! on my USA keyboard) =========== Saved from a previous post: Be aware that any existing date will now be off by 4 years and 1 day. And copying dates between workbooks becomes a problem, too. One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Neall wrote: I have been racking my brain to figure out why my formula is not working but now I see that when reviewing the results through the functional argument tool I see that excel is reading the day (march 31 2010) as 39538. On the spreadsheet it clearly shows me the correct date format but when excel is reading the formula it is not. Can someone help? Thanks -- Neall -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates won't show? | Excel Discussion (Misc queries) | |||
what is the formula to show the difference between two dates | Excel Discussion (Misc queries) | |||
HOW DO I SET DATES TO SHOW ON MY SPREADSHEET? | Excel Worksheet Functions | |||
HOW DO I SET DATES TO SHOW ON MY SPREADSHEET? | Excel Discussion (Misc queries) | |||
Fractions show as dates | Excel Discussion (Misc queries) |