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

[Gary in Pennsyl]

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/a381/garyinpennsylvania/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/a381/garyinpennsylvania/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

 

[Ron Rosenfeld]

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/a381/garyinpennsylvania/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/a381/garyinpennsylvania/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

 

[Daniel CHEN]

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"

 

[Gary in Pennsyl]

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