Problem subtracting dates in excel

Navigation
1

I have a problem wanting to make a difference between two dates to know the days between them. These on the one hand 

3/15/17 17:58
3/15/17 17:57
3/15/17 17:55

and on the other 

2017-03-29 00:05:49 UTC
2017-06-15 00:40:49 UTC
2017-03-15 01:12:21 UTC

In the second one, the UTC hinders me how could I remove it? and if it is necessary to remove the time to use the function dias (date1, date2) if it does not affect the function, I can leave it. I would also like to change the format to have the same, and try to change it by selecting the column and using ctrl + 1 but only change some and leave them the same.

    
asked by Javier Arturo Hernández Sosa 10.01.2018 в 05:55
source

1 answer

1

The first problem (and the most complicated) is the first date that is in m/d/yy format, to convert it into a real date, you have to extract the parts and add 2000 years to convert it properly. The formula would be: 

=FECHA(EXTRAE(A5;ENCONTRAR(" ";A5)-2;2)+2000;EXTRAE(A5;1;ENCONTRAR("/";A5)-1);EXTRAE(A5;ENCONTRAR("/";A5)+1;ENCONTRAR("/";A5;ENCONTRAR("/";A5)+1)-ENCONTRAR("/";A5)-1))

The example cell is A5 . We can not make a cut by fixed positions because the format is variable, both the day and the month can have 1 or 2 characters, so we have to detect the separation characters and make a medium arithmetic complicated with them. Surely with VBA we could use regular expressions, which is a bit simpler.

The other date is easier to convert, since the format is fixed yyyy-mm-dd , with which the extraction of data is more direct. The formula: 

=FECHA(EXTRAE(C6;1;4);EXTRAE(C6;6;2);EXTRAE(C6;9;2))

The base cell in the example is C6

    
answered by 10.01.2018 в 16:36
Doubt compiling cpp and .h file Restart - Clear stack Android