CASH DATE

Question

CASH DATE

Asked 4 years, 1 month ago

Viewed 136 times

-3

I import a spreadsheet where there are several date fields that are the focus of my import, only when it is imported into the database it goes in numerical value, Is there any way to convert these values back to date or into a string with the correct value for those dates? because I need to compare if the sellers informed incorrect dates like Saturday, Sunday or holidays and after these analysis I need to play days ahead to get out of these incorrect dates!

Image for analysis:

Image after the import:

I can work with these values after importing with some date() php method?

I already searched a lot and did not find a way to configure the data in excel before importing, I even changed the formats of the fields in Mysql to string thinking that it would recognize the dates in text, but without success.

I use Laravel Framework and Laravel Excel library for import

This value is the Julian day, that is the number of days since 30/12/1899 (as used by Excel and similar to represent a date). One possible way to work with this number is to convert it to the UNIX_TIMESTAMP recognized by Mysql, subtract 25569 and multiply the result by 246060 that will obtain the amount of seconds elapsed since 01/01/1970. Or convert this number into a string containing the Gregorian date and then import it into your database.

– anonimo

2020/11/01 at 20:35
Another possibility is to create an auxiliary column in Excel containing the formula =TEXTO(célula_contendo_data;"dd/mm/aaaa") which will generate a string with the corresponding date.

– anonimo

2020/11/01 at 20:43

1 answer

Browser other questions tagged php mysql laravel excel

You are not signed in. Login or sign up in order to post.

by Tácio Brito • **186** points · Answer 1 · 2020-11-17T02:36:44+00:00

Use the library https://github.com/PHPOffice/PhpSpreadsheet, she has a resource for this type of case, I myself needed to solve this problem:

First you need to import the Date class from this library:

use PhpOffice\PhpSpreadsheet\Shared\Date;

Then, just use, as for example, I had done a validation too:

  if(is_numeric($data)) {
    $dataConvertida = Date::excelToDateTimeObject($data)->format('Y-m-d');
  }