Share via

Excel internal date format

Anonymous
2019-09-13T20:57:54+00:00

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.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

7 answers

Sort by: Most helpful
  1. Doug Robbins - MVP - Office Apps and Services 323.1K Reputation points MVP Volunteer Moderator
    2019-09-16T09:00:56+00:00

    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

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2019-09-15T12:00:35+00:00

    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.

    Was this answer helpful?

    0 comments No comments
  3. Doug Robbins - MVP - Office Apps and Services 323.1K Reputation points MVP Volunteer Moderator
    2019-09-15T09:13:56+00:00

    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.

    Was this answer helpful?

    0 comments No comments
  4. Anonymous
    2019-09-15T05:42:45+00:00

    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.

    Was this answer helpful?

    0 comments No comments
  5. Anonymous
    2019-09-13T21:05:09+00:00

    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

    Was this answer helpful?

    0 comments No comments