excel - I am getting the overflow error while trying to change a number into a date -

i trying convert numbers -> specified date format. being done on 1 column (column d). here code -

'changing date format (for uploaddate column) application.screenupdating = false each c in range("d2:d" & cells(rows.count, "d").end(xlup).row)     c.value = dateserial(left(c.value, 4), mid(c.value, 5, 2), right(c.value, 2))     c.numberformat = "mm/dd/yyyy" next application.screenupdating = false 

now, whenever code reaches point - breaks following error being displayed :-

run-time error '6': overflow

what code does, overall, copy data excel file hidden sheet of excel (where code located). update column date format (as specified in above code) , update pivot tables in file.

note - set visibility of hidden sheet true before changing format of column

you getting error because cell either has negative value or large value formatted date. may want see explanation ########

see example

test code

sub sample()     dim c range      set c = range("b3")      debug.print dateserial(left(c.value, 4), mid(c.value, 5, 2), right(c.value, 2)) end sub 

step through code , check address of cell c , manually check cell contains.

one way of finding offending cell address using error handling. see example

sub sample()     dim c range      set c = range("b3")      on error goto whoa      debug.print dateserial(left(c.value, 4), mid(c.value, 5, 2), right(c.value, 2))      exit sub whoa:     msgbox "the error happened in " & c.address end sub