COleDateTime base date 30 Dec 1899. Why??

Hello all,
Not really an MFC question as such but I wondered if anybody knew.
Basically, I was working on a (MFC)project where an ADO recordset is 
populated from an SQLServer table with a large range of date records. 
The idea was to get more than I need then use the Filter property to 
show just a limited set. The reason for this was to only have one SQL 
Server round trip to fetch all the records for likely timespans.

Anyway I ran into trouble because the ADO Filter expects dates in local 
format. To cut a long story short I outputted the SQLServer date as a 
floating point number then convert the COleDateTime's also to float and 
filter based upon the floats. This works fine, although you have to take 
into account the different base date for the two data structures. 
SQLServer has a base date of 1st Jan 1900, which seems pretty reasonable 
to me. COleDateTime has a base date of 30th Dec 1899. OK the difference 
is 2 so it doesn't really matter, but I'm struggling to think of a 
reason why that particular date was chosen.

Anyone know why?

Cheers
0
cpeters (15)
5/21/2008 6:57:55 PM
vc.mfc 33608 articles. 0 followers. Follow

7 Replies
2584 Views

Similar Articles

[PageSpeed] 17

I guess it was a "child" mistake of a programmer who wrote this date/time 
part of MS-DOS.
Then MFC COleDateTime class inherited this bug with the DATE type.

Victor

"Colin Peters" <cpeters@coldmail.com> wrote in message 
news:483470b4$1_7@news.bluewin.ch...
> Hello all,
> Not really an MFC question as such but I wondered if anybody knew.
> Basically, I was working on a (MFC)project where an ADO recordset is 
> populated from an SQLServer table with a large range of date records. The 
> idea was to get more than I need then use the Filter property to show just 
> a limited set. The reason for this was to only have one SQL Server round 
> trip to fetch all the records for likely timespans.
>
> Anyway I ran into trouble because the ADO Filter expects dates in local 
> format. To cut a long story short I outputted the SQLServer date as a 
> floating point number then convert the COleDateTime's also to float and 
> filter based upon the floats. This works fine, although you have to take 
> into account the different base date for the two data structures. 
> SQLServer has a base date of 1st Jan 1900, which seems pretty reasonable 
> to me. COleDateTime has a base date of 30th Dec 1899. OK the difference is 
> 2 so it doesn't really matter, but I'm struggling to think of a reason why 
> that particular date was chosen.
>
> Anyone know why?
>
> Cheers 


0
5/21/2008 7:29:07 PM
On Wed, 21 May 2008 20:57:55 +0200, Colin Peters <cpeters@coldmail.com>
wrote:

>Hello all,
>Not really an MFC question as such but I wondered if anybody knew.
>Basically, I was working on a (MFC)project where an ADO recordset is 
>populated from an SQLServer table with a large range of date records. 
>The idea was to get more than I need then use the Filter property to 
>show just a limited set. The reason for this was to only have one SQL 
>Server round trip to fetch all the records for likely timespans.
>
>Anyway I ran into trouble because the ADO Filter expects dates in local 
>format. To cut a long story short I outputted the SQLServer date as a 
>floating point number then convert the COleDateTime's also to float and 
>filter based upon the floats. This works fine, although you have to take 
>into account the different base date for the two data structures. 
>SQLServer has a base date of 1st Jan 1900, which seems pretty reasonable 
>to me. COleDateTime has a base date of 30th Dec 1899. OK the difference 
>is 2 so it doesn't really matter, but I'm struggling to think of a 
>reason why that particular date was chosen.
>
>Anyone know why?

I think it ultimately has to do with Lotus 123, whose date format Excel
copied, including the bug which considered 1900 to be a leap year. Consider
this little program:

#include <afx.h>
#include <ATLComTime.h>
#include <stdio.h>

void print(double serial)
{
   COleDateTime d;
   d.m_status = COleDateTime::valid;
   d.m_dt = serial;
   printf("%f = %s\n", d.m_dt, (LPCSTR) d.Format());
}

int main()
{
   print(59);
   print(60);
   print(61);
   print(62);
}

Its output is:

59.000000 = 2/27/1900
60.000000 = 2/28/1900
61.000000 = 3/1/1900
62.000000 = 3/2/1900

Looks fine. Now consider the same values in Excel:

59	2/28/1900 12:00:00 AM
60	2/29/1900 12:00:00 AM
61	3/1/1900 12:00:00 AM
62	3/2/1900 12:00:00 AM

Again, 1900 was not a leap year, so Excel 2007 is still copying the Lotus
bug and I suppose always will. Notice that the formatted COleDateTime
values are one day less than the Excel values for serial numbers < 61, and
that they agree for serial numbers >= 61; the COleDateTime does not
incorrectly consider 1900 a leap year. So based on this, I'd guess that the
designers of the OLE DATE type wanted to be compatible with Excel, but they
didn't want to copy the Lotus bug, and they were willing to accept being
incompatible with Excel for this small range of early dates. Given the
popularity of Lotus 123 back in the day, the Excel designers no doubt felt
they couldn't afford even this small degree of incompatibility.

-- 
Doug Harrison
Visual C++ MVP
0
dsh (2498)
5/21/2008 8:08:32 PM
Where do you get the link to MS-DOS??

Victor wrote:
> I guess it was a "child" mistake of a programmer who wrote this date/time 
> part of MS-DOS.
> Then MFC COleDateTime class inherited this bug with the DATE type.
> 
> Victor
> 
> "Colin Peters" <cpeters@coldmail.com> wrote in message 
> news:483470b4$1_7@news.bluewin.ch...
> 
>>Hello all,
>>Not really an MFC question as such but I wondered if anybody knew.
>>Basically, I was working on a (MFC)project where an ADO recordset is 
>>populated from an SQLServer table with a large range of date records. The 
>>idea was to get more than I need then use the Filter property to show just 
>>a limited set. The reason for this was to only have one SQL Server round 
>>trip to fetch all the records for likely timespans.
>>
>>Anyway I ran into trouble because the ADO Filter expects dates in local 
>>format. To cut a long story short I outputted the SQLServer date as a 
>>floating point number then convert the COleDateTime's also to float and 
>>filter based upon the floats. This works fine, although you have to take 
>>into account the different base date for the two data structures. 
>>SQLServer has a base date of 1st Jan 1900, which seems pretty reasonable 
>>to me. COleDateTime has a base date of 30th Dec 1899. OK the difference is 
>>2 so it doesn't really matter, but I'm struggling to think of a reason why 
>>that particular date was chosen.
>>
>>Anyone know why?
>>
>>Cheers 
> 
> 
> 
0
cpeters (15)
5/21/2008 8:10:57 PM
Doug Harrison [MVP] wrote:
> On Wed, 21 May 2008 20:57:55 +0200, Colin Peters <cpeters@coldmail.com>
> wrote:
> 
> 
>>Hello all,
>>Not really an MFC question as such but I wondered if anybody knew.
>>Basically, I was working on a (MFC)project where an ADO recordset is 
>>populated from an SQLServer table with a large range of date records. 
>>The idea was to get more than I need then use the Filter property to 
>>show just a limited set. The reason for this was to only have one SQL 
>>Server round trip to fetch all the records for likely timespans.
>>
>>Anyway I ran into trouble because the ADO Filter expects dates in local 
>>format. To cut a long story short I outputted the SQLServer date as a 
>>floating point number then convert the COleDateTime's also to float and 
>>filter based upon the floats. This works fine, although you have to take 
>>into account the different base date for the two data structures. 
>>SQLServer has a base date of 1st Jan 1900, which seems pretty reasonable 
>>to me. COleDateTime has a base date of 30th Dec 1899. OK the difference 
>>is 2 so it doesn't really matter, but I'm struggling to think of a 
>>reason why that particular date was chosen.
>>
>>Anyone know why?
> 
> 
> I think it ultimately has to do with Lotus 123, whose date format Excel
> copied, including the bug which considered 1900 to be a leap year. Consider
> this little program:
> 
> #include <afx.h>
> #include <ATLComTime.h>
> #include <stdio.h>
> 
> void print(double serial)
> {
>    COleDateTime d;
>    d.m_status = COleDateTime::valid;
>    d.m_dt = serial;
>    printf("%f = %s\n", d.m_dt, (LPCSTR) d.Format());
> }
> 
> int main()
> {
>    print(59);
>    print(60);
>    print(61);
>    print(62);
> }
> 
> Its output is:
> 
> 59.000000 = 2/27/1900
> 60.000000 = 2/28/1900
> 61.000000 = 3/1/1900
> 62.000000 = 3/2/1900
> 
> Looks fine. Now consider the same values in Excel:
> 
> 59	2/28/1900 12:00:00 AM
> 60	2/29/1900 12:00:00 AM
> 61	3/1/1900 12:00:00 AM
> 62	3/2/1900 12:00:00 AM
> 
> Again, 1900 was not a leap year, so Excel 2007 is still copying the Lotus
> bug and I suppose always will. Notice that the formatted COleDateTime
> values are one day less than the Excel values for serial numbers < 61, and
> that they agree for serial numbers >= 61; the COleDateTime does not
> incorrectly consider 1900 a leap year. So based on this, I'd guess that the
> designers of the OLE DATE type wanted to be compatible with Excel, but they
> didn't want to copy the Lotus bug, and they were willing to accept being
> incompatible with Excel for this small range of early dates. Given the
> popularity of Lotus 123 back in the day, the Excel designers no doubt felt
> they couldn't afford even this small degree of incompatibility.
> 

That makes sense. So the base date for Excel was 31 Dec 1899, 00:00 
a.m.? I wonder why the base date for SQL server is a day later....copied 
from Oracle?

BTW the original problem revolved around using the Filter property of an 
ADO recordset, and that the Filter date format isn't directly compatible 
with the SQL date format. My solution of comparing the dates cast as 
doubles (with the 2 day offset) had a little potential bugette. Namely 
that the Filter is a string whci I composed using the CString::Format 
method. This also uses local convention, so rather than getting 
"DateReal >= 32590.234 and DateReal <= 32595.123" I got "DateReal >= 
32.590,234 and DateReal <= 32.595,123" on a German OS. Which ADO won't 
parse correctly, IMHO. So it seems that ADO filter dates ought to be in 
local format, but other numbers not.

Thanks for your time Doug, that little conumdrum was going round my head 
for ages.



0
cpeters (15)
5/22/2008 6:59:10 PM
On Thu, 22 May 2008 20:59:10 +0200, Colin Peters <cpeters@coldmail.com>
wrote:

>That makes sense. So the base date for Excel was 31 Dec 1899, 00:00 
>a.m.? I wonder why the base date for SQL server is a day later....copied 
>from Oracle?

Who knows? I always liked the way Plauger concluded Chapter 15 in "The
Standard C Library":

Exercise 15.9 [Very hard] Devise a more orderly way to measure time. Get
somebody with some authority to agree to adopt it.

Seems like this was done multiple times, for many definitions of "orderly".
All kidding aside, all these methods are basically the same, and after
implementing the library time functions, I think he was wishing for a
system that did away with leap years, leap seconds, time zones, and
everything else that makes time programming so much fun.

>BTW the original problem revolved around using the Filter property of an 
>ADO recordset, and that the Filter date format isn't directly compatible 
>with the SQL date format. My solution of comparing the dates cast as 
>doubles (with the 2 day offset) had a little potential bugette. Namely 
>that the Filter is a string whci I composed using the CString::Format 
>method. This also uses local convention, so rather than getting 
>"DateReal >= 32590.234 and DateReal <= 32595.123" I got "DateReal >= 
>32.590,234 and DateReal <= 32.595,123" on a German OS. Which ADO won't 
>parse correctly, IMHO. So it seems that ADO filter dates ought to be in 
>local format, but other numbers not.
>
>Thanks for your time Doug, that little conumdrum was going round my head 
>for ages.

No problemo.

-- 
Doug Harrison
Visual C++ MVP
0
dsh (2498)
5/22/2008 7:57:24 PM
On Wed, 21 May 2008 15:08:32 -0500, "Doug Harrison [MVP]" <dsh@mvps.org>
wrote:

>I think it ultimately has to do with Lotus 123, whose date format Excel
>copied, including the bug which considered 1900 to be a leap year. Consider
>this little program:
>
>#include <afx.h>
>#include <ATLComTime.h>
>#include <stdio.h>
>
>void print(double serial)
>{
>   COleDateTime d;
>   d.m_status = COleDateTime::valid;
>   d.m_dt = serial;
>   printf("%f = %s\n", d.m_dt, (LPCSTR) d.Format());
>}
>
>int main()
>{
>   print(59);
>   print(60);
>   print(61);
>   print(62);
>}
>
>Its output is:
>
>59.000000 = 2/27/1900
>60.000000 = 2/28/1900
>61.000000 = 3/1/1900
>62.000000 = 3/2/1900
>
>Looks fine. Now consider the same values in Excel:
>
>59	2/28/1900 12:00:00 AM
>60	2/29/1900 12:00:00 AM
>61	3/1/1900 12:00:00 AM
>62	3/2/1900 12:00:00 AM
>
>Again, 1900 was not a leap year, so Excel 2007 is still copying the Lotus
>bug and I suppose always will. Notice that the formatted COleDateTime
>values are one day less than the Excel values for serial numbers < 61, and
>that they agree for serial numbers >= 61; the COleDateTime does not
>incorrectly consider 1900 a leap year. So based on this, I'd guess that the
>designers of the OLE DATE type wanted to be compatible with Excel, but they
>didn't want to copy the Lotus bug, and they were willing to accept being
>incompatible with Excel for this small range of early dates. Given the
>popularity of Lotus 123 back in the day, the Excel designers no doubt felt
>they couldn't afford even this small degree of incompatibility.

FWIW, it looks like I guessed right. See:

http://www.inc.com/magazine/20080701/how-hard-could-it-be-glory-days.html

-- 
Doug Harrison
Visual C++ MVP
0
dsh (2498)
6/27/2008 3:19:05 PM
Very nice article!
Thank you, Doug!

Victor

"Doug Harrison [MVP]" <dsh@mvps.org> wrote in message 
news:b31a645bdhl1l55e8s88vdhefvbijai8ug@4ax.com...
> On Wed, 21 May 2008 15:08:32 -0500, "Doug Harrison [MVP]" <dsh@mvps.org>
> wrote:
....
> FWIW, it looks like I guessed right. See:
>
> http://www.inc.com/magazine/20080701/how-hard-could-it-be-glory-days.html
>
> -- 
> Doug Harrison
> Visual C++ MVP 


0
6/28/2008 3:09:53 PM
Reply:

Similar Artilces:

Calculate 1st of month date from existing date.
I want Excel to post a starting date which is 45 days (or other time period) from an initial date. The rules are: (1) If the date is under 15 (middle of the month) set the starting date to the 1st of the month as calculated and (2) if the date is 15 or over, set starting date to the 1st of the next month. For instance, if my starting date is January 1st, 2006 and I want the date 45 days from there, the answer is February 15, 2006. Reset the calculated value of February 15, 2006 to February 1, 2006. If the date was February 16, 2006 (46 days after start), set the date to March 1, 2006. Th...

Error when trying to Create Purchase Order based on re-order information
When I try to create a new purchase order based on re-order information the status says Identifying items to be ordered, then an error pops up saying Run-time error '3251': Object or provider is not capable of performing requested operations. Store Operations Manager then closes. Can someone help me figure this out as I can't really place orders until I get this figured out. check this KB article: 0 http://support.microsoft.com/kb/910710 -- Ahmed Nashat http://ahmed.nashat.googlepages.com <tfittsy@gmail.com> wrote in message news:47342429-dfa8-4fc3-99df-6f31085f33dc...

Date conditioning format problem
I am using excel 2000. with my cursor in A1, I select cell A1 to A20 which is already in date format, and do a conditioning format(highlight) using IsFormula: =$A$1=TODAY() Unfortunately, all the cells from A1 to A20 are highlighted!!!!!! no matter the dates is today or otherwise!!!!!!!! In fact all the cells are having the same conditioning format which is =$A$1=TODAY(). How to make it suvch that cell AX is having =$A$X=TODAY()conditioning format??? Hi Take the Absolute off the row Reference =$A1=TODAY() Regards Roger Govier pfnus@hotmail.com wrote: > I am using excel 2000. >...

Date numbering out of order in Outlook 07
Hi guys, I am a function organiser and within Outlook I have mail files for each event I am running. So for example, I would have a mail folder set up that looks like this: 12 December 1 Dec Pharmacy Guild 6 Dec 15 Dec Event Central 25 Dec Christmas Day But what I find is that Outlook puts things in a different order than I would expect and so I have ended up with this: [IMG]http://i14.tinypic.com/6kz8umq.jpg[/IMG] Is there a work around (other than renaming every sub-folder a-z)? Thanks in advance for help on sorting in order, Jo Actually, none of your sub-folders a...

DTS Export fails on bad date
While trying to import an excel spreadsheet to a SQL Server table, DTS fails, pointing to a column with date values. I have looked at all the date values, and they appear correct. I need to get this excel spreadsheet loaded to a database table. Is there any way to pinpoint the exact cell causing the problem? Or, can you think of another way to export the data in the spreadsheet besides DTS that might not be so sensitive to data content? Thanks, Dean Slindee ...

Validation list based on criteria
Hi I want to create a data validation list based on certain conditions. Let me try to explain by example. A1 = oranges B1 = oranges A2 = apples B2 = no record A3 = pears B3 = pears A4 = pears B4 = pears A5 = grapes B5 = grapes A6 = apples B6 = no record B:B value is based on a relative formula determined by the value in A A:A can have duplicate values I want to create a validation list based on B:B where the formula result is “no record” The list needs to be populated with the relative value in A For example: Since B2 and B6 = “no record” then the validat...

How do I change the US date setting in the event schedule planner
Help! I'm using a lovely little template called event schedule planner - however it shows the date in US date format and I need the UK format (dd.mm.yyyy) how do I change it in this template please? If this is Word 2007/2010 then the chances are that it uses date content controls to display the dates. You can change them all with a macro Sub Macro1() Dim oCC As ContentControl For Each oCC In ActiveDocument.ContentControls If oCC.Type = wdContentControlDate Then oCC.DateDisplayLocale = wdEnglishUK oCC.DateDisplayFormat = "dd.MM.yyyy" End ...

comment box with date
Is there a way to have a comment automatically open with the days date next to the user name? You could use a simple macro like this and add it to a button on a toolbar: Sub InsertComment() With ActiveCell .AddComment .Comment.Text Text:="name" & Chr(10) & _ Format(Now, "mm/dd/yy") & Chr(10) .Comment.Visible = True End With End Sub HTH Jason Atlanta, GA >-----Original Message----- >Is there a way to have a comment automatically open with >the days date next to the user name? >. > ...

disc date for next month
We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- 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" button in the me...

Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,">="&DATE(1900,1,1))-COUNTIF(E:E,">="&DATE(2010,2,5))+COUNTIF(E:E,DATE(2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas???? =SUMPRODUCT(--(E1:E100<=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: > IM trying to create a formula that gives me...

Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou individually typing up each one -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450 View this thread: http://www.excelforum.com/showthread.php?threadid=38137 Is there a way to insert it the date into the the worksheet tab (not th cells in the actual worksheet but the name of the worksheet itself -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: ht...

The ability to change multiple activity due dates at once
I would find it extremely helpful when I have multiple activities that are due on the same date and I want to change the due date to be able to change them all at once rather than having to open each one separately and then changing the due date. ---------------- 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" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree&...

Add time to a date time group
Hi, thanks for any help in advance I have my date and time displayed in this format dd hhmm "K" mmm yy which shows up as 03 1800 K Sep 08. I would like a formula that will add 30 min to this time group so it becomes 03 1830 K Sep 08 Assume your date/time is in A1, then you can do this in another cell: =3DA1+30/24/60 Format the cell in the same way as A1 (using the Format Painter icon). Times are stored in Excel as fractions of a 24-hour day, hence the divisions by 24 and 60. Hope this helps. Pete On Sep 4, 12:28=A0am, Patrick <Patr...@discussions.microsoft.com> wr...

Add text to a field based on a combo box selection
I'm making a form that contains a dropdown field (dropdown1) containing 5 possible selections. Based upon what the user selects, I want to fill a second field (text3) with a corresponding value. For instance: If dropdown1="Maintenance" then enter "Jack Doe" text3 If dropdown1="Safety" then enter "Tom Collins" text 3 etc. Is there a way to write a 'if then else' formula in a 'calculate' form field? And if so what would the syntax be? Or is there a better (easier, non-programatic) way to accomplish this? I'm not...

Help with "base unit" in chart axis
I have two charts. In one, when I go to "format axis" I can enter the min and max scale as dates and the entry remains as dates when I come back. I can also set the "base unit" a major and minor grid line in days, weeks, months, etc. The second chart, I do not have a base unit drop down box, and although I can enter dates in the max and min in date format and they work, when I come back the entries are in days since the start of time (38353 in 1/1/2005) And I cannot specify the units of major and minor grid lines, they are just units. Which makes it awkward for minor gi...

Money using wrong date to calculate ROI YTD
I have opened a new file in Money to track investments for a stock club. I entered all transactions, which go back to Jan. 2002. When I look at performance for the account, the ROI YTD is not accurate, as Money is using values for the stocks going back only 6 months form the date I enterd the transactions rather than the values as of 1/1/03. What to do? In microsoft.public.money, Brooks wrote: >I have opened a new file in Money to track investments for >a stock club. I entered all transactions, which go back >to Jan. 2002. When I look at performance for the account, &g...

How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion dates by a certain percentage per year, how do I write the formula. For example if my project starts May 26, 2010 and lasts until may 26 2014 I want to add escalation to midpoint at 4% per year. ...

How can i use a command button to validate date and time
I have two command buttons, each on a different sheet. i need both of them to put the current date in one cell on its sheet and current time in another on its sheet. i also need these cells to be locked so the user can only edit these cells by clicking the command button. Any answers? ...

Re: Is there a way to HIDE a row based on a value of a cell ?
Hi Reddance, Try instead: Sub Tester() Dim rng As Range Dim i As Long If LCase(Sheets("Ctrl").Range("A1").Value) _ <> "yes" Then Exit Sub End If With Sheets("ToPrint") For i = 5 To .UsedRange.Rows.Count Step 2 If Not rng Is Nothing Then Set rng = Union(rng, .Cells(i, "A")) Else Set rng = .Cells(i, "A") End If Next i End With If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If ...

Resource Units
I want to be able to have a resource, say 'electrician', with 5 available for the day shift and 2 available for the night shift. This would allow e to just assign the 'electrician' resource and have project level based on unit availability. If I setup 'electrician Day' and 'electrician Night', then I need to carefully select which to use and if the schedule shifts it is a nightmare. Any thought on how to do this? Thank-you! On Jan 6, 10:43=A0pm, John K <John K...@discussions.microsoft.com> wrote: > I want to be able to have a resou...

Make A Set Of Date
i have 2 sheets FOGLIO1 e FOGLIO2 in FOGLIO1 cell D2 i have the value 60 in the cell J2 i have a date, for example 27/11/2004, i colud want fill a set of date in FOGLIO2 to start in the cell G2 for every 60 month day year, for example: sheet FOGLIO2 in the cell G2 27/11/2004 in the cell G3 27/12/2004 in the cell G4 27/01/2005 in the cell G5 27/02/2005 .... in the cell BN2 27/10/2009 this macro for every data present in the column D2:D2000 and in the column J2:J2000 from FOGLIO1 when the actual date is the same in the cell G2 of sheet FOGLIO2 delete the cell G2 and skip to left the other c...

Date expression in Query
I am getting 'undefined function' w/this expression in my qry. Expr1: IIf(today()<[PoP_End_Dt],"cur","exp") How do I insert or use todays date in a Query. Expr1: IIf(Date()<[PoP_End_Dt],"cur","exp") -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Steve Stad" wrote: > I am getting 'undefined function' w/this expression in my qry. > Expr1: IIf(today()<[PoP_End_Dt],"cur","exp") > > How do I insert or use to...

Last Saved Date #3
Excel 2003 In Word 2003 it is possible to insert a field that will show the date and time that the document was last saved. I have been trying to find something similar for Excel that I can insert as part of a custom footer, or at least in a cell somewhere on the page. How can I get the date and time of the last save to show in an Excel file? --Tom To place in a footer.................. Sub foot_date() ActiveSheet.PageSetup.RightFooter = "Last Saved : " & _ Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _ "yyyy-...

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

Date cells after conversion from Database
In our organisation we are dealing with different date formats (dd/mm/yyyy or mm/dd/yy) When information is requested out of our JDE database it is presented as a csv file. After convertion into Excel the 'dd/mm/yyyy' are recognised as date formats, but the 'mm/dd/yy' only as general formats. How do I change those last ones in recogniseable date formats? Thanks for your help!!!! Rene. if you can highlight them all then right click and choose format cells then under the number tab choose custom then enter mm/dd/yy in the to box. that might help cheer -- Message po...