Excel returns dd/mm/yyyy as 1/0/00 when performing time calculations.

[rberard]

Hello,

I have been trying to locate a quick fix but can't remember where I had seen it. Something about my excel workbook not having the correct format in the setting or something like that.

I am using the cloud version of Excel.

Thanks in advance for your assistance and time.

 

[mole999]

if you look at the cell value, anything to the left of the decimal is date, and the right is time. Installed language can effect the output

 

[rberard]

Thanks mole999 sorry but that really does not help me.

When I format the cell under Date and select 3/14/12 1:30 PM even the sample that is shown as 1/0/00.

When I do Ctrl/Shift/Colon the format regardless of what I format the cell to always revert to 1/0/00

So something is amiss and I just can't understand what.

 

[joeu2004]

When I format the cell under Date and select 3/14/12 1:30 PM even the sample that is shown as 1/0/00.

Is that date/time calculated? I wonder if the workbook is in Manual calculation mode. Click Formulas > Calculation Options.

When I do Ctrl/Shift/Colon the format regardless of what I format the cell to always revert to 1/0/00

Admittedly, that does not jibe with my theory above. But I don't understand what you are saying. When you do ctrl+:, do you see the current time initially, formatted correctly? If yes, what do you do next to cause it to "revert" to 1/0/00?

 

[joeu2004]

Something about my excel workbook not having the correct format in the setting or something like that.

Right. That is just Excel's way of denoting that the date part is zero. As you might know, Excel time (and date) is store as integer date number (1 = 1 Jan 1900) plus fractional time (1/24 = 1 hour).

I am not familiar with "cloud" versions of Excel: Excel Online or Office 365. But generally, there might be nothing that you can do automatically.

For some calculations, Excel automatically changes the cell format to d/m/yyyy (in your region) h:mm:ss. I often have to change the format manually. (Of course, I could create an event macro to do the same.)

Also, sometimes Excel displays the zero date in the Formula Bar. We cannot control the FB format.

That said, perhaps there is indeed some global format option that affects what you are seeing. I cannot say unless you provide a concrete example with values, formulas and manual operations (like ctrl+:, as you mention later).

Take a look at the Region and Language control panel and the Advanced Options to be sure that everything is what you expect.

 

[joeu2004]

Sorry if my posts are misdirections and unhelpful. I wish I could delete ill-considered postings in this forum.

I'm going to bug out this discussion. I just realized that I don't understand your problem descriptions, and I don't have the time now to give you the kind of well-considered comments and follow-up questions that you deserve.

Good luck!

 

[rberard]

The reply was not directed toward you...

I am just wondering why Excel won't keep d/m/y for 2017 and continuously reverts to 1/0/00.

I am trying to add x number of minutes to the current date/time in one cell to determine a future date/time in another cell.

I really do appreciate your assistance and have checked the setting. Work calculation is set to Automatic and Language is set to Match MS Window.

Regards

 

[joeu2004]

The reply was not directed toward you.

If you are referring to your reply to mole999, I know. My comments were a self-assessment, not in response to you.

I am trying to add x number of minutes to the current date/time in one cell to determine a future date/time in another cell.

Well, we really need to see (constant) values and formulas to offer a well-considered explanation or remedy.

But that is precisely the kind of operation where I have seen Excel change the cell format arbitrarily and seemingly "randomly". I have never figured out why or when. Sometimes =A1+TIME(0,1,0) retains the cell format, and sometimes it (or some similar formula) does not.

And importantly, I have never found a way to stop Excel from making the change. Poor product design, IMHO. FYI, the same thing happens with some currency calculations, especially when using financial functions like PMT and NPV.

There are Excel Options that cause Excel to inherit the format of surrounding cells. You might check to be sure they are disabled. I disable all of Excel's "intelligent" options. I believe Excel cannot read my mind. I hope I can do a much better job of that. (Smile)

I [...] have checked the setting. Work calculation is set to Automatic and Language is set to Match MS Window.

But I remembered later: you are using a "cloud" version of Excel. I have no experience with that. And I believe that "cloud" versions have their own online "preferences" that might influence Excel behavior, independent of your local computer configuration. (Caveat: A possible misdirection.)

Nevertheless, based on your clarification above, I would not have much of hope of "fixing" the problem. Bad product design, as I said.

(Well, again, there is always the possibility of writing an event macro. I don't have time to offer guidance. And IMHO, it is a huge waste of time and effort.)

Good luck!