add/subtract dates problem in Excel.....

Hi folks!

I usually never get my butt kicked by Excel. More often that not, I eventually find a go around searching Google.com

Now I'm stumped. ( and frustrated! )

My employer uses SAP as its logistics/production control/management software. The dates in SAP ( unchangeable by us end users ) are standard German DD.MM.YY
When I copy from SAP into Excel.....or export from SAP to Excel, this date format goes along.
Here is a sample screenshot:
http://i15.photobucket.com/albums/a3...lvania/xls.jpg

As you can see, I have a formula ( imbedded If's ) in column J. Excel didn't recognize this date setup, so I highlighted all, format the cells to United Kingdom date:
http://i15.photobucket.com/albums/a3...nia/UKDate.jpg

AT first, it looked like the formula worked........But the logic is false.
here is my formula:
=IF(G4=H4,0,(IF(G4>H4,"Released Late","Released Early")))
But Excel isn't looking at the objects in the cells as dates.....but just the first valus before the first "." period.
In other words, Excel sees 15.04.06 ( 15 April 2005 ) as GREATER than 10.05.06 ( 10 May 06 ).

What do I do? I want to either add/subtract these dates as they are.....or get Excel to convert these German dates to English format.

What are your thoughts?

Thanks!

Gary in Pennsylvania

On Wed, 31 May 2006 12:35:04 -0500, Gary in Pennsyl
<[email protected]> wrote:

>
>Hi folks!
>
>I usually never get my butt kicked by Excel. More often that not, I
>eventually find a go around searching Google.com
>
>Now I'm stumped. ( and frustrated! )
>
>My employer uses SAP as its logistics/production control/management
>software. The dates in SAP ( unchangeable by us end users ) are
>standard German DD.MM.YY
>When I copy from SAP into Excel.....or export from SAP to Excel, this
>date format goes along.
>Here is a sample screenshot:
>http://i15.photobucket.com/albums/a3...lvania/xls.jpg
>
>As you can see, I have a formula ( imbedded If's ) in column J. Excel
>didn't recognize this date setup, so I highlighted all, format the
>cells to United Kingdom date:
>http://i15.photobucket.com/albums/a3...nia/UKDate.jpg
>
>AT first, it looked like the formula worked........But the logic is
>false.
>here is my formula:
>=IF(G4=H4,0,(IF(G4>H4,"Released Late","Released Early")))
>But Excel isn't looking at the objects in the cells as dates.....but
>just the first valus before the first "." period.
>In other words, Excel sees 15.04.06 ( 15 April 2005 ) as GREATER than
>10.05.06 ( 10 May 06 ).
>
>What do I do? I want to either add/subtract these dates as they
>are.....or get Excel to convert these German dates to English format.
>
>What are your thoughts?
>
>Thanks!
>
>Gary in Pennsylvania

Most likely the dates are text, and not real Excel dates; in addition, they may
be terminated by a no-break space (char(160)).

One method of converting these dates into "Excel" dates is with the formula:

=DATE(MID(G3,7,4),MID(G3,4,2),LEFT(G3,2))

You can then add/subtract/format etc however you wish.

Another method to convert would be to select the column, then use the Data/Text
to Columns wizard. When you get to Step 3, select Date and DMY.


--ron

Assume dd.mm.yyyy in cell B7, use the following formula for conversion:

=DATE(RIGHT(B7,4),MID(B7,FIND(".",B7,1)+1,FIND(".",B7,4)-FIND(".",B7,1)-1),LEFT(B7,FIND(".",B7,1)-1))

This converts dd.mm.yyyy OR d.m.yyyy OR d.mm.yyyy to mm/dd/yyyy.


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================
"Gary in Pennsyl"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi folks!
>
> I usually never get my butt kicked by Excel. More often that not, I
> eventually find a go around searching Google.com
>
> Now I'm stumped. ( and frustrated! )
>
> My employer uses SAP as its logistics/production control/management
> software. The dates in SAP ( unchangeable by us end users ) are
> standard German DD.MM.YY
> When I copy from SAP into Excel.....or export from SAP to Excel, this
> date format goes along.
> Here is a sample screenshot:
> http://i15.photobucket.com/albums/a3...lvania/xls.jpg
>
> As you can see, I have a formula ( imbedded If's ) in column J. Excel
> didn't recognize this date setup, so I highlighted all, format the
> cells to United Kingdom date:
> http://i15.photobucket.com/albums/a3...nia/UKDate.jpg
>
> AT first, it looked like the formula worked........But the logic is
> false.
> here is my formula:
> =IF(G4=H4,0,(IF(G4>H4,"Released Late","Released Early")))
> But Excel isn't looking at the objects in the cells as dates.....but
> just the first valus before the first "." period.
> In other words, Excel sees 15.04.06 ( 15 April 2005 ) as GREATER than
> 10.05.06 ( 10 May 06 ).
>
> What do I do? I want to either add/subtract these dates as they
> are.....or get Excel to convert these German dates to English format.
>
> What are your thoughts?
>
> Thanks!
>
> Gary in Pennsylvania
>
>
> --
> Gary in Pennsyl
> ------------------------------------------------------------------------
> Gary in Pennsyl's Profile:
> http://www.excelforum.com/member.php...o&userid=34978
> View this thread: http://www.excelforum.com/showthread...hreadid=547172
>

Assume dd.mm.yyyy in cell B7, use the following formula for conversion:

=DATE(RIGHT(B7,4),MID(B7,FIND(".",B7,1)+1,FIND(".",B7,4)-FIND(".",B7,1)-1),LEFT(B7,FIND(".",B7,1)-1))

This converts dd.mm.yyyy OR d.m.yyyy OR d.mm.yyyy to mm/dd/yyyy.


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================
"Gary in Pennsyl"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi folks!
>
> I usually never get my butt kicked by Excel. More often that not, I
> eventually find a go around searching Google.com
>
> Now I'm stumped. ( and frustrated! )
>
> My employer uses SAP as its logistics/production control/management
> software. The dates in SAP ( unchangeable by us end users ) are
> standard German DD.MM.YY
> When I copy from SAP into Excel.....or export from SAP to Excel, this
> date format goes along.
> Here is a sample screenshot:
> http://i15.photobucket.com/albums/a3...lvania/xls.jpg
>
> As you can see, I have a formula ( imbedded If's ) in column J. Excel
> didn't recognize this date setup, so I highlighted all, format the
> cells to United Kingdom date:
> http://i15.photobucket.com/albums/a3...nia/UKDate.jpg
>
> AT first, it looked like the formula worked........But the logic is
> false.
> here is my formula:
> =IF(G4=H4,0,(IF(G4>H4,"Released Late","Released Early")))
> But Excel isn't looking at the objects in the cells as dates.....but
> just the first valus before the first "." period.
> In other words, Excel sees 15.04.06 ( 15 April 2005 ) as GREATER than
> 10.05.06 ( 10 May 06 ).
>
> What do I do? I want to either add/subtract these dates as they
> are.....or get Excel to convert these German dates to English format.
>
> What are your thoughts?
>
> Thanks!
>
> Gary in Pennsylvania
>
>
> --
> Gary in Pennsyl
> ------------------------------------------------------------------------
> Gary in Pennsyl's Profile:
> http://www.excelforum.com/member.php...o&userid=34978
> View this thread: http://www.excelforum.com/showthread...hreadid=547172
>

Assume dd.mm.yyyy in cell B7, use the following formula for conversion:

=DATE(RIGHT(B7,4),MID(B7,FIND(".",B7,1)+1,FIND(".",B7,4)-FIND(".",B7,1)-1),LEFT(B7,FIND(".",B7,1)-1))

This converts dd.mm.yyyy OR d.m.yyyy OR d.mm.yyyy to mm/dd/yyyy.


--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================
"Gary in Pennsyl"
<[email protected]> wrote in
message news:[email protected]...
>
> Hi folks!
>
> I usually never get my butt kicked by Excel. More often that not, I
> eventually find a go around searching Google.com
>
> Now I'm stumped. ( and frustrated! )
>
> My employer uses SAP as its logistics/production control/management
> software. The dates in SAP ( unchangeable by us end users ) are
> standard German DD.MM.YY
> When I copy from SAP into Excel.....or export from SAP to Excel, this
> date format goes along.
> Here is a sample screenshot:
> http://i15.photobucket.com/albums/a3...lvania/xls.jpg
>
> As you can see, I have a formula ( imbedded If's ) in column J. Excel
> didn't recognize this date setup, so I highlighted all, format the
> cells to United Kingdom date:
> http://i15.photobucket.com/albums/a3...nia/UKDate.jpg
>
> AT first, it looked like the formula worked........But the logic is
> false.
> here is my formula:
> =IF(G4=H4,0,(IF(G4>H4,"Released Late","Released Early")))
> But Excel isn't looking at the objects in the cells as dates.....but
> just the first valus before the first "." period.
> In other words, Excel sees 15.04.06 ( 15 April 2005 ) as GREATER than
> 10.05.06 ( 10 May 06 ).
>
> What do I do? I want to either add/subtract these dates as they
> are.....or get Excel to convert these German dates to English format.
>
> What are your thoughts?
>
> Thanks!
>
> Gary in Pennsylvania
>
>
> --
> Gary in Pennsyl
> ------------------------------------------------------------------------
> Gary in Pennsyl's Profile:
> http://www.excelforum.com/member.php...o&userid=34978
> View this thread: http://www.excelforum.com/showthread...hreadid=547172
>

I managed to manipulate the way SAP was exporting the date. That fixed the date format into a configuration that Excel recognized.

Mission accomplished!

It's nice to know this forum is available!

Gary in Pennsylvania