Solved Reports

[Vineeth Mohan]

Hi,

I was pulling out the reports from some of my extension numbers to externam numbers. In the report the date & time field has different formats. Some cells have fomat General and some are custom. For general I can use the formula =LEFT(B10,10) and the result is fine. But when the cells are custom format this formula doesnt work. Why do i have different formats for the same field in the same report. How do i resolve the issue

 

[Vineeth Mohan]

Hi,

I was pulling out the reports from some of my extension numbers to externam numbers. In the report the date & time field has different formats. Some cells have fomat General and some are custom. For general I can use the formula =LEFT(B10,10) and the result is fine. But when the cells are custom format this formula doesnt work. Why do i have different formats for the same field in the same report. How do i resolve the issue

I could also notice the some date and time fields are 24 hr format and some has AM/PM

13/11/2021 4:35:39 AM
10/11/2021 2:19

 

[VasilisV_3CX]

Hi @Vineeth Mohan,

I believe the reason this happens is because excel isn't sure about 10/11 - it could be the 11th of the 10th month or the 10th of the 11th month. Whereas 13/11 can only ever be the 13th of the 11th month.

If you paste in excel, you can choose to import it as text, this won't change the format at all. If you export your report to .csv you'll see that the date isn't different between the two lines in the report - so therefore this is an issue with how Excel/Sheets perceives the data it's receiving.

 

[Vineeth Mohan]

@VasilisV_3CX
I tried exporting the report as csv and then on the same file the same field has different formats.

 

[VasilisV_3CX]

Hi @Vineeth Mohan,

Which field are you referring to in this example you mentioned above? If you open the .csv that you downloaded you'll notice that the format doesn't change in the .csv before the import.

 

[Vineeth Mohan]

@VasilisV_3CX I had shared you the report in private. Please check'
Thanks in advance

 

[VasilisV_3CX]

To update: @Vineeth Mohan sent through a report which he requested assistance with to separate date and time. I've advised him in PM and will continue the conversation there.