Forum Home
Press F1
Thread ID: 66685	2006-03-04 03:41:00	Excel Formulas	csinclair83 (200)	Press F1

Post ID	Timestamp	Content	User
435351	2006-03-04 21:56:00	It sounds as if the dates are being recognised as a text field, not actually a date field, hence the value error. In the date cell, edit - clear - formats Enter the date in the form of 1 feb 2006 That always forces Excel into a date format for me.	godfather (25)
435352	2006-03-04 22:02:00	It sounds as if the dates are being recognised as a text field, not actually a date field, hence the value error.It sounded that way to me too, GF, but I tried setting some or all of the date fields to be text fields and the functions still worked as expected... I think it might have something to do with both the Windows Regional Settings and the Excel date format settings somehow... Not sure how yet :) Mike.	Mike (15)
435353	2006-03-04 22:04:00	Ok i opened a new excel sheet, so its clean, no background of formulas, entered 2 dates....1st febuary 2006 and 10th May 2006...and it gave a answer, it was "0" and that was using the formula =days360(1/2/2006,10/5/2006) (entered the dates manually in formula)....so tried 1/2/2006 and 10/5/2006 in the 2 cells before the days formula cell....said i gave 2 few arguments????	csinclair83 (200)
435354	2006-03-04 22:06:00	It sounded that way to me too, GF, but I tried setting some or all of the date fields to be text fields and the functions still worked as expected... I think it might have something to do with both the Windows Regional Settings and the Excel date format settings somehow... Not sure how yet :) Mike. Just in case you might want to know.. Windows reginal settings are... timezone: GMT+12 auckland/wellington and i've just found that the dates are showing as american instead of 5/3/20006 its showing 3/5/2006.... is there a way to adjust??	csinclair83 (200)
435355	2006-03-04 22:06:00	d.v.c.What does d.v.c. stand for godfather? diligentia vis celeritas? da vinci code? Something else? Mike.	Mike (15)
435356	2006-03-04 22:11:00	Just in case you might want to know.. Windows reginal settings are... timezone: GMT+12 auckland/wellington and i've just found that the dates are showing as american instead of 5/3/20006 its showing 3/5/2006.... is there a way to adjust??Those are your timezone settings, not your regional settings ;) Go into Control Panel, and open "Regional Settings and Language Options" (I assume you're using Windows XP), on the "Regional Options" tab, make sure that "English (New Zealand) is selected in the first drop-down list, and "New Zealand" is selected in the second drop-down list. This should be enough (although if you want you can go through the other tabs and change all the settings to be New Zealand), click OK, then open Excel and type in a date - check your cell date format to make sure it's set to "English (New Zealand)", and have another go at your formula. Mike.	Mike (15)
435357	2006-03-05 00:18:00	sorry went for lunch! anyway.. changed regional settings as you said, and restarted computer (its request) then i opened a brand new spreadsheet, selected 10 cells and right clicked and selected date, and the dd/mm/yyyy setting... entered 1/1/2003 and 1/2/2003 and then formula days360(a2:b2)....it gave result of too few arguments.. so removed that formula, entered a2-b2, then it gave me a negative answer well it said "negative dates or times are shown as ###" as the cell was showing ############### guess we have a answer...but its not showing the number of days....	csinclair83 (200)
435358	2006-03-05 00:31:00	guess we have a answer...but its not showing the number of days....No, it is answering correctly now! You're just entering the formula slightly wrong... Firstly you need to replace the colon with a comma: =days360(a2:b2) should be =days360(a2,b2) days360(a2:b2) will always give a "too few arguments" error. Secondly, in the second attempt you subtracted the greater date from the lesser date - it should have been the other way around: =a2-b2 should be =b2-a2 Mike.	Mike (15)
435359	2006-03-05 00:36:00	Hey i adjusted it... b2-a2 the answer given was...31/01/1900 but....i changed formula to days360(a2,b2) and it gave the answer as 30....(dates used in a2 and b2 are 3/12/2003 and 3/1/2004) so thats correct :D	csinclair83 (200)
435360	2006-03-05 00:39:00	Hey i adjusted it... b2-a2 the answer given was...31/01/1900 That is correct - select that cell and change format to General and you will get the correct result :) but....i changed formula to days360(a2,b2) and it gave the answer as 30....(dates used in a2 and b2 are 3/12/2003 and 3/1/2004) so thats correct :DCool :D Mike.	Mike (15)
1 2 3