Home

Time data issues

%3CLINGO-SUB%20id%3D%22lingo-sub-363771%22%20slang%3D%22en-US%22%3ETime%20data%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363771%22%20slang%3D%22en-US%22%3E%3CP%3EAfter%20selecting%20custom%20h%3Amm%20in%20a%20cell%20when%20I%20type%209.15%20it%20comes%20up%20as%207%3A12%20or%20something.%20Why%3F%20Also%2C%20in%20the%20same%20cell%2C%20if%20I%20double%20click%20it%20shows%20the%20date%20%26amp%3B%20time%2C%20i.e.%2022%2F8%2F2018%207%3A12AM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20response%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-363771%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363830%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20data%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363830%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Rino%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Excel%20dates%20and%20times%20are%20digital%20numbers.%20Date%20is%20integer%20part%20of%20the%20number%20and%20they%20start%20from%20Jan%2001%2C%201900%20which%20is%20equal%20to%201%20(thus%20equivalent%20of%20Mar%2010%2C%202019%20will%20be%20the%20number%2043534).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETime%20is%20decimal%20part%2C%201%20hour%20%3D%201%2F24.%20Thus%206pm%20%3D%2024%2F18%20%3D%200.75%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormatting%20like%20h%3Amm%20is%20to%20operate%20with%20dates%20and%20time%20in%20more%20human%20friendly%20manner.%20Thus%20if%20you%20type%209.15%20it%20is%20the%20equivalent%20of%209%20days%20(integer%20part)%20and%203.6%20(24*0.15)%20hours.%20With%20General%20format%20you%20see%209.15%2C%20with%20time%20format%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20199px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F86340iBBDC5B167A72C3C2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFormula%20bar%20shows%20full%20date%20%26amp%3B%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Rino Comodo
Visitor

After selecting custom h:mm in a cell when I type 9.15 it comes up as 7:12 or something. Why? Also, in the same cell, if I double click it shows the date & time, i.e. 22/8/2018 7:12AM

 

Any response would be greatly appreciated.

1 Reply

Hi Rino,

 

In Excel dates and times are digital numbers. Date is integer part of the number and they start from Jan 01, 1900 which is equal to 1 (thus equivalent of Mar 10, 2019 will be the number 43534).

 

Time is decimal part, 1 hour = 1/24. Thus 6pm = 24/18 = 0.75

 

Formatting like h:mm is to operate with dates and time in more human friendly manner. Thus if you type 9.15 it is the equivalent of 9 days (integer part) and 3.6 (24*0.15) hours. With General format you see 9.15, with time format

image.png

Formula bar shows full date & time.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies