WWII IBM POW punch card dates

[John Duresky]

I'm trying to convert dates from post-WWII IBM punch cards which gave the dates when men were captured after the fall of Bataan and Corregidor. These were punched in as five-digit codes as Day Month Year. In an Excel spreadsheet I have, those POWs show for example 09042 which signifies April 9, 1942, the day Bataan fell, or e.g., 13124 which would be December 13, 1944, the day a pilot may have been captured. I need a formula which gives these in Excel date format which somehow adds 194 to that last single digit for the year, and also in American Month Day Year format. Unless we can convert those five-digit codes to an Excel date format we have no good way of sorting that column by date. Incidentally, it contains the names of 46,209 men taken prisoner during WWII in the Pacific, and of those about 40% died as POWs. I'm the main researcher for the book we published in August last year which details the life of La Crosse, WI native, Army 1st Lt Chester K Britt, and the men he served with, titled RELENTLESS HOPE - A True Story of War and Survival, by David L Britt (youngest son of Chester) with John Duresky (myself) and Vickie Graham ISBN 978-1-09838-539-2 Chester was on the Bataan Death March and much more as a POW. Thanks, John

 

[Joe4]

Welcome to the Board!

Can you tell us how the values in column F are currently formatted?
They appear to be right-justified without comments, suggesting that they are actually entered as numbers.
But numbers would not show leading zeroes without a special format.
So we just want to see what format is currently being used.

Alternatively, they could also be dates with a special date format.

Lastly, and most unlikely, they could be text (string) entries.

Knowing the exact format of the data is important in devising a solution to work for you. We could "guess", but we might guess wrong.

 

[JamesCanale]

MrExcelPlayground16.xlsx
	A	B
1	10042	4/10/1942
2	28123	12/28/1943
3	7052	5/7/1942
4	7052	5/7/1942
5	28123	12/28/1943
Sheet10

Cell Formulas
Range	Formula
B1:B5	B1	=DATE(1940+RIGHT(A1,1),INT(A1/10)-INT(A1/1000)*100,INT(A1/1000))

 

[John Duresky]

Set up as Custom Number with a leading zero. Original list might have had for example 9042 so changed to be five wide in 00000 format to add the leading zero for day so gives any day from 01 to 31

 

[Joe4]

James,

That formula does not handle the blank and "00000" entries he shows in cells F2 and F3 correctly. I think you need to add an IF statement in there.

Here is the formula that I came up with that should handle all those cases:

=IF(F2>0,DATE("194"&RIGHT(F2,1),MID(TEXT(F2,"00000"),3,2),LEFT(TEXT(F2,"00000"),2)),"")

 

[John Duresky]

Plugged that in to row 2 and dragged it down. Doesn't seem to work, see attached screen capture

 

[Joe4]

Plugged that in to row 2 and dragged it down. Doesn't seem to work, see attached screen capture

That is working - you need to now choose a Date format for that column!

It is important to note how Excel stores dates. It stores them as numbers, namely the number of days since 1/0/1900.
So dates are really just number with special date formats.

Choose your desired date format for that column, and you should see that it is working.

 

[John Duresky]

Bingo. Works perfectly, thanks very much. The late 1Lt Chester K Britt and the men and women he served with and I thank you.

 

[Joe4]

You are welcome.
We are glad we could help!

 

[Joe4]

I changed the solution to the thread which actually contained the formulas that converted the data. That is the key to the solution.
The formatting is just how you want to present the results.