Time / Each Second Bewtween 9:30 AM and 4:15 PM

[carl]

I a trying to generate a column that will list each second from 9:30Am until
4:15 PM.

My time format needs to look like this:

Time

93000

93001

93002

etc

93059

93100

93102

etc

95959

100000

100001

100002

etc

161500

Where the last entry 161500 is 4:15 PM.

Is there a good way to do this ?

Thank you in advance.

 

[E-Coder]

If you select the range of cells that contain these times, then use a custom
format:

"hhmmss"

 

[Bernie Deitrick]

Carl,

Format the column for custom, using the format string HMMSS. Then in row 2 of that column, enter
the formula

=((ROW()-2)+9.5*3600)/86400

and copy down to row 24,302.

HTH,
Bernie
MS Excel MVP

 

[Peo Sjoblom]

That's a lot of rows!

One way

in A1 put

=--TEXT("09:30"+ROWS($A$1:A1)/24/60/60,"hhmmss")

then copy down to A24300

best way is probably to type in

A1:A24300 in the name box, then pressing Ctrl + D

then finally select A1:A24300 and do copy and paste special as values

It will take a while to copy down a formula 24300 rows so I hope you have a
powerful computer