View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
MartinW MartinW is offline
external usenet poster
 
Posts: 860
Default Is there a way to enter times without typing a colon?

Hi Dennis,

One way

A1 0930 (Start time)
A2 1430 (Start lunch)
A3 1500 (End Lunch)
A4 2130 (Finish time)
A5
=((CEILING(A4,25)-CEILING(A1,25))-((CEILING(A3,25)-CEILING(A2,25))))/100

This will round to the nearest quarter hour.
You will need a different approach if your shift
crosses midnight.

Format A1 to custom 0000

HTH
Martin