Thread: Summing numerical substrings.

I tried to do date calculation in excel but seems like couldnt get the result which I want.I extracted data from SAP with a standard date being 12.02.2004 when I paste it into the Excel and tried to do the calculation eg. 12.12.2005 - 12.02.2004 in Excel.it gives me and error message.

How am I going to make sure that the substraction between these two dates will give me and answer in days.

Pls note that I am a layman in using Excel formulas.fyi i dont want to change the date to 12/02/05 instead I would prefer it to be as waht I originally received it.

Pls help.Thanks in advance.

Zilfar

=VALUE(LEFT(B2, LEN(B2)-2)) and sum it all

Problem:

Listed in columns A:B are file names and their sizes (KB, MB or GB).
How could we total MB file sizes only?

Solution:

Using the FIND and LEFT text category functions, in the following Array formula:
{=SUM(IF(ISNUMBER(FIND(C8,B2:B5)),VALUE(LEFT(B2:B5,FIND(C8,B2:B5)-2)),0))}

File name______File Size
file1__________20 MB
file2__________ 30 KB
file3__________ 1.2 GB
file4__________ 400 MB

Doesn't the "-2" require you to know in advance how long each of the letter sequences you're removing are? Answer is sorta misleading anyway, since after conversion, it should be around 1600 MB and change (since base 10 is only approximate).