If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Start, Finish including break time
Hi all,
I currently have a excel spreadsheet set up to take the following. Just an example: Cell A1 - Time Started (In 24 hour time) - "06:30" Cell A2 - Break Time (Generally Half an hour) - "00:30" Cell A3 - Finish Time - "16:30" Total - =(A3-A2-A1)*24 Which gives me a result in hours which I wanted. "9.5" However When entering in the break time I want to be able to enter it in as ..5 (for half and hour) instead of having to enter in 00:30 for 30 minutes and still calculate the correct response at the end. Also, i'd like to be able to enter the times in without having to enter the : in, for example "0630" (starting at 06:30) and "1630" (finishing at 16:30). Could someone please let me know whether the above is possible, and if maybe you have some suggestions for cleaning it up it would be appreciated. Regards, Ricardo |
#2
|
|||
|
|||
Start, Finish including break time
Chip Pearson gives two macros to do that for dates and times he
http://www.cpearson.com/excel/DateTimeEntry.htm Hope this helps. Pete On Aug 25, 8:46*am, Ricardo wrote: Hi all, I currently have a excel spreadsheet set up to take the following. Just an example: Cell A1 - Time Started (In 24 hour time) - "06:30" Cell A2 - Break Time (Generally Half an hour) - "00:30" Cell A3 - Finish Time - "16:30" Total - =(A3-A2-A1)*24 Which gives me a result in hours which I wanted. "9.5" However When entering in the break time I want to be able to enter it in as .5 (for half and hour) instead of having to enter in 00:30 for 30 minutes and still calculate the correct response at the end. Also, i'd like to be able to enter the times in without having to enter the : in, for example "0630" (starting at 06:30) and "1630" (finishing at 16:30). Could someone please let me know whether the above is possible, and if maybe you have some suggestions for cleaning it up it would be appreciated. Regards, Ricardo |
#3
|
|||
|
|||
Start, Finish including break time
Format A1 and A3 as TEXT
=TEXT(A3-A1,"00\:00")*24-A2 "Ricardo" wrote: Hi all, I currently have a excel spreadsheet set up to take the following. Just an example: Cell A1 - Time Started (In 24 hour time) - "06:30" Cell A2 - Break Time (Generally Half an hour) - "00:30" Cell A3 - Finish Time - "16:30" Total - =(A3-A2-A1)*24 Which gives me a result in hours which I wanted. "9.5" However When entering in the break time I want to be able to enter it in as .5 (for half and hour) instead of having to enter in 00:30 for 30 minutes and still calculate the correct response at the end. Also, i'd like to be able to enter the times in without having to enter the : in, for example "0630" (starting at 06:30) and "1630" (finishing at 16:30). Could someone please let me know whether the above is possible, and if maybe you have some suggestions for cleaning it up it would be appreciated. Regards, Ricardo |
Thread Tools | |
Display Modes | |
|
|