I have an issue with copy/pasting datetime information in Excel. I have a sheet where I need to create hourly datetime values for some price analysis, and the calculations themselves work just fine; it adds an hour to each previous hour as I would expect.
The issue is that when it comes to the 12am time specifically, the cell will say, for instance 1/2/19 12:00 AM, but the formula bar will show 1/1/2019 12:00:00 AM. I don't really care what the formula bar says, but that appears to be the value Excel is actually using. If I copy/paste:value the date and then compute copy-pasted it equals exactly 1.
So to summarize, my formulas to calculate datetime are fine on the surface, but for some reason the actual values differ from what is shown and what should actually be the value.
I'm using Excel 2016.
I honestly don't know where to start with the formula bar problem, but I have tried copy/pasting in almost any way imaginable, both by changing formats of the 'copy area' and the 'paste area', even formatting as text then formatting back to date.
I think something that's interesting is when I paste the datetime in, it initially shows the correct date in the cell, but when I hit f2 on the cell is switches to the previous day; right time, wrong day.
I'll try to upload the file if I can.
Thanks in advance!
I was able to figure it out. While I still don't know why the displays were different, I found that when using the time() function, if entering time(24,,) you get a 0 value, so I just switched it to (hour/24).