Internal date format

  • Thread starter Thread starter Bob Greene
  • Start date Start date
B

Bob Greene

I'm trying to set up a column that is formatted MM/DD/YY HH:MM, with each
cell 1 minute apart.

I thought I could format the first cell as a number after entering the
starting date and time, and then increment the number by the appropriate
step, but I think I may get "drift"; i.e. today is 37942.5986 (11/15/03
14:22). Adding a "step" of .0007 increments the MM portion of the formatted
column, but will it creep higher after many steps?

What step should I use if I want 5 minute or 10 minute increments?

What is the internal format that Excel uses for date/time?

Thanks for your help.....
 
Use 2 cells, first cell

01/01/03 10:00

second

01/01/03 10:05

now select both cells and copy down

one hour is 1/24, one minute is 1/24/60
 
Bob

Enter your date/time in A1 and format to your liking.

In A2 enter =A1 + 1/1440 and drag/copy.

The resulting times will be incremented by 1 minute. I get no drifting down
to row 655536.

5 minutes would be 5/1440, etc.

Gord Dibben XL2002
 
I'm trying to set up a column that is formatted MM/DD/YY HH:MM, with each
cell 1 minute apart.

I thought I could format the first cell as a number after entering the
starting date and time, and then increment the number by the appropriate
step, but I think I may get "drift"; i.e. today is 37942.5986 (11/15/03
14:22). Adding a "step" of .0007 increments the MM portion of the formatted
column, but will it creep higher after many steps?

What step should I use if I want 5 minute or 10 minute increments?

What is the internal format that Excel uses for date/time?

Thanks for your help.....
Click to expand...

If 11/15/03 14:22 is in A1,

To increment by 5 minutes: =A1 + TIME(0,5,0)
To increment by 10 minutes: =A1 + TIME(0,10,0)

Excel stores date/time as days and fractions of a day. So the above is
equivalent to:

=A1 + 5/1440
=A1 + 10/1440

Your 0.0007 is actually a bit more than one minute. One minute would be
0.0006944444444... or 1/1440.


--ron
 
Back
Top