Converting number into time in same cell in Excel

I'm creating schedule manager and I encountered this:

The user is prompted to type a number when he wants to start doing a certain task, for example number "13" should represent 13:00. But after he types this number, I need it to show as 13:00.

]

In order to do this, I need to divide the number by 24 and then this number is convertible into the format I want (13:00). But I have no idea how to insert this number back into the same cell.

Is it possible, or is there another way to do this? It should also work if I type in half hours (13:30).

2 Answers

MAJOR EDIT

Based on the feedback, this is a major rewrite of the original post.

Instead of converting the number in the same cell, you can convert the number during calculations. With this approach, the user thinks that they are entering time in 24 hour format, but in reality it is an integer that gets converted to time. Below is a sample.

Column H contains a description of what is in Column G for that row.

The key for the user is to enter the times in 24 hour format without the colon, which is noted in the banner at the top of the sheet. If you format the time cells (rows 3, 4, and 6) with a custom format of ##":"00 they will look like they are displaying a standard time format.

Here are the cell content and formulas:

G3: Enter End Time here (24 hour format without the colon)
Value entered in example is 1645 but will display as 16:45

G4: Enter Start Time here (24 hour format without the colon)
Value entered in example is 820 but will display as 8:20

G5: =TIME(INT(G3/100),MOD(G3,100),0)-TIME(INT(G4/100),MOD(G4,100),0)+IF(G3<=G4,1,0) (8:25)

This subtracts the two times and corrects for negative time errors (+IF(G3<=G4,1,0)).

The TIME function takes input as hours, minutes, seconds TIME(h,m,s). Since G3 & G4 are integers, the hours are found by dividing by 100 (INT(G3/100) or the digits displayed to the left of the :), the minutes by taking the remainder of that division (MOD(G4,100) or the digits displayed to the right of the :), and the seconds are zero since we are dealing with whole minutes.

The result will be an actual time value and the cell should be formatted as time or custom (h:mm).

G6: Enter Break/Lunch Time here (24 hour format without the colon)
Value entered in example is 30 but will display as :30

G7: =(G5-TIME(INT(G6/100),MOD(G6,100),0))*24 (7.92)

This converts G6 to a time format, as described above for cell G5, and subtracts the two times.

The difference is multiplied X24 to give an answer in decimal hours because my system required that type of entry. If that is what you need, then format the cell as a number with 2 decimal places.

If you don't need decimal hours, then just leave our the *24 from the formula and format the cell as time.

In my full sheet, Column G is repeated for each day of the week. Hope this helps.

This Macro, will convert the Number into Time according to 24 Hrs clock time in the selected Cell/Cells.

Sub NumberToTime() Dim rCell As Range Dim iHours As Integer Dim iMins As Integer For Each rCell In Selection If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then iHours = rCell.Value \ 100 iMins = rCell.Value Mod 100 rCell.Value = (iHours + iMins / 60) / 24 rCell.NumberFormat = "h:mm AM/PM" End If Next
End Sub

How it works:

• Enter this Macro as Module with the Sheet.
• Enter the Time as Number shown in the Screen Shot.
• Select the Data Range & Run the Macro.

You get the desire results as shown in the Screen Shot.