Grouping Dates

[Deepak Sharma]

Hello Everyone,

I have compiled one file in GOOGLE Sheets for various employees for their hours worked date wise. Then copied that data in Excel, But when I am making Pivot table & grouping the dates, it's not working.
The reason might be:-
1. On every first row (Date cell) there is an Import range formula(Which I used in google sheets to compile the sheets), but when I copied & pasted data in excel I clicked values only.
2. There are blank rows also between the employees' data.
3. There might be other date formate chosen by the employees somewhere in between.

I mean I tried everything but I couldn't be succeeded.

Please see if something can happen else I have to manually do this for more than 1 Lakh Rows.
File Attached!

Regards,
Deepak Sharma

 

[Hui]

A lot of the dates in Column A are not actually Dates, they are text
eg: A2, A1977 etc

You could fix this using a helper Column
eg: D2: =IFERROR(IF(ISNUMBER(A2),A2,DATEVALUE(A2)),0)
copy that down to D48418

Set the date format in Column D eg d mmm yy

Now when you take the data into a Pivot table
Then group


enjoy

 

[Deepak Sharma]

Hi Hui, Thanks for your reply!
When I copy down the above formula only five digit no. is displaying, for eg: 42822, 42824, that might be the cell address. With that Yes I can get rid of the blank cells but column A will still contain the dates as text in few cells.

Thanks !

 

[Hui]

Read carefully



Those 5 digits Numbers are the Integer numbers for the Dates
You have to apply a Custom Number format (Ctrl+1) to make them appear as dates, even though they are still numbers internally

 

[Deepak Sharma]

Yes, Hui It's working now, Thank you So So Much! It gonna save a lot of time.
Thank you

Regards,
Deepak Sharma