format of copied data unclear

[Khurram]

Hi all,
I'm copying three values from one worksheet to another using VBA. The
source of all three are formulae but I am only pasting the values and
formats at destination using PasteSpecial. All three source values
have the Cell Format "Custom" set to "hh:mm" and the visible values
are "00:31", "03:19" and "21:05" respectively.

On the destination however although the visible values remain the
same, the underlying values are now "01/01/1900 00:31:13",
"01/01/1900 03:19:39" and "21:05:49" respectively which interferes
with some of my formulae on the destination sheet. As you can see the
third value seems to be different from the rest and this is how I
would like the first and second values. I've checked everything but
have no idea what is causing this difference. Can anyone let me know
what I should be looking for?

Thank you kindly

Khurram


P.S Below is my code just in case

'Open Batch file
Workbooks.Open Filename:="C:\" & selectedMonth & " Batch Metrics.xls"

'Get Average Time APS Main Batch complete
Sheets("Batch Metrics").Cells.Find("Average").Select
ActiveCell.Offset(6, 5).Copy
Windows(2).Activate
Sheets("SD Summary").Select
Cells(1, 1).Select
ActiveSheet.PasteSpecial xlPasteValuesAndNumberFormats

'Get Average Time APS 2nd Batch start
Windows(2).Activate
Sheets("Batch Metrics").Cells.Find("Average").Select
ActiveCell.Offset(6, 2).Copy
Windows(2).Activate
Sheets("SD Summary").Select
Cells(2, 1).Select
Selection.PasteSpecial xlPasteValuesAndNumberFormats

'Get Average Time APS 2nd Batch complete
Windows(2).Activate
Sheets("Batch Metrics").Cells.Find("Average").Select
ActiveCell.Offset(6, 6).Copy
Windows(2).Activate
Sheets("SD Summary").Select
Cells(3, 1).Select
Selection.PasteSpecial xlPasteValuesAndNumberFormats

'Close Batch data file
Workbooks("Feb Batch Metrics.xls").Close SaveChanges:=False

 

[Khurram]

Figured out the anomoly. The source data for the "21:05" had invalid
date values attached which meant that when a formula was used to
calculate these values, all the date related data was ignored. So
when it was copied, I didnt get any date values with it. Not the case
for the other two values.

Khurram