Excel internal date format

Question

I am using Microsoft Excel 2019 MSO (16.0.11929.20234) 32-bit

If I set the format of a cell (say cell B4) to be a custom format dd-mm-yy hh:mm, and then

insert the current date and time by pressing Ctrl+; (semi-colon), then pressing Space, and then pressing Ctrl+Shift+; (semi-colon),

the result is 14 09 2019 06:36

Then a formula INT(B4) gives the #VALUE error.

Under Microsoft Excel 14.0.7232.5000 (32-bit) if I do the same thing,

the result is 14-09-2019 06:36

Then a formula INT(B4) gives the result 43722, which is correct.

So something has changed.

In either case, I expected to be able to use DATEVALUE(B4) to obtain the result 43722.27, because I understood the

internal representation of the date/time is a numeric value. This does not seem to be the case any more.

Please help.

Answer 1

Using a 64-bit installation of Microsoft Office  ProPlus, Version 1908 (Build 11929.20300 Click-to-Run) Monthly Channel and entering the date and time by using CTRL+; then pressing the space bar and entering the time by using CTRL+SHIFT+; (which is actually CTRL+:), both =INT(B4) and =VALUE(B4) return the expected values when the cells containing those formulae are formatted as General

Answer 2

Thank you for your reply Doug. It has helped me to refine my understanding of the source of the problem.

I think the difficulty is related to the manner of data entry. Here's what I tried:

(a)  Format the B4 cell as d/mm/yyyy h:mm and then insert the current date by

      pressing Ctrl+; (semi-colon),

      and then press Enter:

      the result shown in the cell is                15/09/2019 0:00               and

      both Int(B4) and Value(B4) return the expected date value (43723).

      This is as expected, and is correct.

(b)  Format the B4 cell as d/mm/yyyy h:mm and then insert the current date and time by

      pressing Ctrl+; (semi-colon), then pressing Space, and then pressing Ctrl+Shift+; (semi-colon),

      and then press Enter:

      the result shown in the cell is                15 09 2019 21:37             and

      both Int(B4) and Value(B4) return the #VALUE! error.

I am using Microsoft Excel 2019 MSO (16.0.11929.20298) 32-bit

I am using Firefox version 69.0 (64-bit).

I am using a LEVONO ideapad 330 Laptop.

Answer 3

Both Int(B4) and Value(B4) return the expected date value (43723 smd 43723.79 respectively) in a 32-bit installation of Microsoft Office 365 ProPlus - Version 1908 (Build 11929.20300 Click-to-Run) when B4 contains 15/09/2019 19:04 that was inserted into that cell using the method detailed after formatting B4 as d/mm/yyyy h:mm.  Likewise in a 64-bit installation of the same Version and Build of Office.

Answer 4

Thank you for your reply Takmil.  Of course you are right.

I wrongly wrote DATEVALUE; I should have said VALUE, like you said.

But the point is that VALUE(B4) gives the #VALUE! error.

This error still occurs if I format the cell as general.

Ian P.

Answer 5

Hi,

DATEVALUE is not used like this. DATEVALUEreturns the Date Serial Number for a date strored as text. If your date is already a valid date, DATEVALUE will return an error. You can however get the value of the cell using VALUE or by formatting the cell as general.

Regards,

Takmil