entering numbers to display a time format
 

I am trying to add time format to a cell to show just hh:mm if a single
number is typed in ie 14 to show as 14:00 or 14.23 to show as 14:23.

the current format hh:mm seems to default to date and time when a number is
typed in cell such as 14 will show 00:00:00 in the cell and the fomula bar
shows dd:mm:yyyy hh:mm:ss.

would finally like to find a formula which will calculate a timespan between
two cells displaying a time value.. ie start and finish times in seperate
cells then night and day totals to be shown in their respective cells for a
bespoke wages/ time sheet .

ie ;start time D4 =( 23:45 ) finish time E4 =(10:00 ) Night hours F4 (
6.25) Day hours G4 =(4.00)

anyone help me??

For the first part, see http://www.cpearson.com/excel/DateTimeEntry.htm

For the second, use

=(MOD(B1-A1,1)-(B1-"06:00"))*24 for the night hours

=(B1-"06:00")*24 for the day hours

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ian Williams/Eazygig" <Ian
wrote in message ...
I am trying to add time format to a cell to show just hh:mm if a single
number is typed in ie 14 to show as 14:00 or 14.23 to show as 14:23.

the current format hh:mm seems to default to date and time when a number
is
typed in cell such as 14 will show 00:00:00 in the cell and the fomula bar
shows dd:mm:yyyy hh:mm:ss.

would finally like to find a formula which will calculate a timespan
between
two cells displaying a time value.. ie start and finish times in seperate
cells then night and day totals to be shown in their respective cells for
a
bespoke wages/ time sheet .

ie ;start time D4 =( 23:45 ) finish time E4 =(10:00 ) Night hours F4 (
6.25) Day hours G4 =(4.00)

anyone help me??