Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Time: 11.45 = 11:45

Is there a way to be able to type it in a number (i.e. 11.45) and have it
display as a time (11:45)? When I format the cell to display as time I have
to type the hour, a colon, and minutes. Is there any way around this?


Post your answer or comment

comments powered by Disqus
Hello..

I have long been looking for a solution to my problem, but couldn't figure it out myself. I attached some pictures to make my situation more clear.

Basically what I am looking for is a way to be able to plot different times to a graph. I have for example one table which reads:
time 11:30 - value 2
time 12:45 - value 4
time 13:15 - value 3 And now a second table which reads for example:
time 11:45 - value 1
time 13:00 - value 3
time 13:30 - value 4 What I want is a way to combine these things and be able to create a diagram/graph of these two. Basically I only have time values in steps of 15 minutes (11:15, 11:30, 11:45). I was not able to figure that one out, so until now I just used the running numbers in front of the time stamps, but as you can see that is complete crap.. In other words, I am trying to combine the two tables into one graph and 'unify' the values.

Here is my current example for a data set:

Here is a picture of the whole table:

(click for larger view)

And this is how I want it to basically look like.. I drew it by hand and used the above quoted made up values as data:

Thanks for your help!

Regards,
faithleSs

I am trying to figure out lapse time in minutes from 2 different cells.

Start time 7:05 am
end time 11:45 am

resulting in 280.

The following is a schedule we have for our church daycare. We have three groups of kids. Is there a way with VBA or a formula to create a schedule from this schedule so that the three groups are not doing the same thing at the same time. Except for Breakfast, Snack Time and Lunch. I hope this made since. If not I will try to explain better.

6:00 a.m.-7:15 a.m. open center / free time
7:15 a.m.-7:30 a.m. bathroom break / wash hands
7:30 a.m.-8:00 a.m. breakfast
8:00 a.m.-8:15 a.m. clean-up / wash hands
8:15 a.m.-8:30 a.m. story / group time
8:30 a.m.-9:45 a.m. free play
9:45 a.m.-10:00 a.m. bathroom break / wash hands
10:00 a.m.-10:15 a.m. snack time
10:15 a.m.-10:25 a.m. bathroom break
10:25 a.m.-11:15 a.m. outside / gross motor activity
11:15 a.m.-11:45 a.m. craft time
11:45 a.m.-12:00 p.m. clean-up / bathroom break
12:00 p.m.-12:30 p.m. lunch
12:30 p.m.-3:00 p.m. nap
3:00 p.m.-3:30 p.m. wake-up / bathroom break / quiet time
3:30 p.m.-4:00 p.m. free play / center time
4:00 p.m.-4:10 p.m. bathroom break / wash hands
4:10 p.m.-4:30 p.m. snack time
4:30 p.m.-6:00 p.m. free choice / outside ( pick up time )

Thanks
Gary

Hi Folks!

I'm doing an employee schedule spreadsheet, and for a variety of reasons and formulas I'm allowing the scheduler to input the IN TIME and OUT TIME on one sheet and in TWO different cells, such as:

A1: IN TIME: B1: 8:00 AM
A2: OUT TIME: B2: 4:00 PM

However, on the page that is actually printed for the employee, I need to reserve (reduce) space, so I need to use a conversion that allows me IN ONE CELL to show:

C1: 8 - 4 (I don't need or want the AM or PM I need to raw format).

The problem is, I've tried CONCATENATE, "&", and other TEXT functions, and as you know, when you mix numbers and TEXT, it reduces the value to it's raw number value.

On the INPUT page, all cells are formatted to TIME value, so when they type in 8:00, it will show 8:00 AM. This helps the scheduler. However, when I try to combine in one cell with text on the printable page, it shows .3333333. When I take that cell and multiply it by 24 (hours) it works for some times and not others.

I have been able to resolve this in Military format only, but the staff is not used to that. I don't want to show "16 - 23" or (4:00 PM to 11:00 PM).

Ideally, I'd like the printable page (that the staff sees) to see

8 - 4
5:30 - 11:15,
10 - 3
etc.

I also don't want .50 or .35, I need it broken down into 1/4 hours, such 11:15, 11:30, 11:45, or any TIME variable inbetween.

I'm having alot of trouble with this. Any ideas? Thank you so much!

EDR

Hi,

Being looking for solution and can't get it...

I need to calculate the hrs between two date and time and deduct any time outwith the hrs of operation (09:00-19:00), if the date and time falls outwith these times.
I have used the formula 24*IF(A2>G2,G2+1-A2,G2-A2), where G2 IS 21/04/2008 11:45 and A2 is 20/04/2008 00:22, but can't get it right.

Any help is very much appreciated.

Thanks,

I inherited a spreadsheet...a time sheet, actually. Whoever created it used Excel as a wordprocessor. I just copied it, and figured I'd fix it later. This is what I have:
Time is entered as 1:45-11:45, etc. for shifts. I didn't design this 'sheet' (it's not one at all), and I know this is a stretch, but can I convert these numbers in the cell and then subtotal the hours at the bottom of a column?

Can anyone tell me how calculate date and time..for example how many hours is from:

1/2/03 11.45 am to 1/4/03 2.30 pm

I cannot seem to be able to come up with a workable formula.

Thanks
Anne

Hi All,

If we have date and time entries in separate cells in the record how to compare times with other record’s time to determine which tame comes first. For example if first record has in A1 11/12/06 and in B1 10:25 AM, and next record has in A2 11/09/06 and in B2 11:45 AM. Second record’s time is bigger tan first record’s time but second record should come first because date is less than in first record.
Or other ideas.

Thank you in advance

Hi I have built a workbook that when you enter a start and finish time it write one across the shift. Now I want to take it one step further by adding breaks and lunches.

This is how I obtain my start and finish time.
=IF($G5G$396,1,0),0)
I want to know if i can add to this looking for a break / lunch / break.
shift bring back 8:00 - 16:00 then i want it to look at break one 09:30 and brings a B for one slot back instead of a 1 then look for the lunch 11:45 brings back LB for two slots and the last break 14:30 and brings back a B for one slot.

one slot = 15 minutes.
Can anyone help.

Andy

My code looks like:

(in cell I2)
=IF(B5-H2

First, Thanks to all the great people on this board for the two years of putting together one of the most complicated and comprehensive forms any BOSS can think of for one guy to do. Started 6/2005 and will end 8/2007, with a little more help with what I have no idea how to do.

I have 15 drivers each has 5 loads, that is 75 random arrival times. (AAP –in ) In the cell above the arrival time is manually entered either a “1” or a “B”. If the cell has a “1” then look at the arrival time and number (1,2,3,4,5 and so on) it in the order of it’s arrival time into the cell below the arrival; time. If the cell has a “B” in it is not counted Skipped)
Cell H11, AAP. in =1=7:15pm = 1
Cell H11, AAP. in =B=7:30pm = (Skip. Do not count)
Cell H11, AAP. in =1=9:30pm = 2
Cell H11, AAP. in =1= 9:45pm = 3
Cell H11, AAP. in =1=12:00am = 4
and so on through 75 loads. Let see something like ??? If ( H11 >0, then lookup H12, then put 1,2,3, ect. Into cell H13. Here a small sample of my form I am hoping you all can help.
******** ******************** ************************************************************************>Microsoft Excel - Daily Loadout Sheet Vic's Sample 1.xls.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG8J8M8P8S8G10J10M10P10S10G11J11M11P11S11F12H12I12K12L12N12O12Q12R12T12G16J16M16P16S16G18J18M18P18S18G19J19M19P19S19F20H20I20K20L20N20O20Q20R20T20=
FGHIJKLMNOPQRST81Jameson 3 1Jameson 3 1Jameson 3 2Manning 2Manning 9LPAAR/LRAAPLPAAR/LRAAPLPAAR/LRAAPLPAAR/LRAAPLPAAR/LRAAP10Out6:15 pInOut8:30 pInOut11:00 pInOut1:15 aInOut3:25 aIn1116:45 p119:00 p1111:30 p111:45 a113:55 a1125:45 p 7:15 p8:00 p 9:30 p10:30 p 12:00 a12:45 a 2:15 a2:55 a 4:25 a13 b l b bb 14 15 161Jameson 3 1Jameson 3 1Jameson 3 2Manning 2Manning 17LPAAR/LRAAPLPAAR/LRAAPLPAAR/LRAAPLPAAR/LRAAPLPAAR/LRAAP18Out6:30 pInOut8:45 pInOut11:15 pInOut1:30 aInOut3:40 aIn1917:00 pB19:15 p1b11:45 p112:00 a114:10 a1206:00 p 7:30 p8:15 p 9:45 p10:45 p

I have the following code:
Code:
Public NextTime As Double
Private Sub CheckBox1_Click()
    ScheduleUpdate Me.CheckBox1.Value
End Sub
Sub ScheduleUpdate(ByVal status As Boolean)
    On Error Resume Next
    If status Then Call LinkUpdates
    Application.OnTime EarliestTime:=TimeValue("8:45 AM"), Procedure:="ScheduleUpdate", Schedule:=status
    Application.OnTime EarliestTime:=TimeValue("9:45 AM"), Procedure:="ScheduleUpdate", Schedule:=status
    Application.OnTime EarliestTime:=TimeValue("10:45 AM"), Procedure:="ScheduleUpdate", Schedule:=status
    Application.OnTime EarliestTime:=TimeValue("11:45 AM"), Procedure:="ScheduleUpdate", Schedule:=status
    Application.OnTime EarliestTime:=TimeValue("12:45 PM"), Procedure:="ScheduleUpdate", Schedule:=status
    Application.OnTime EarliestTime:=TimeValue("1:45 PM"), Procedure:="ScheduleUpdate", Schedule:=status
    Application.OnTime EarliestTime:=TimeValue("2:45 PM"), Procedure:="ScheduleUpdate", Schedule:=status
    Application.OnTime EarliestTime:=TimeValue("3:45 PM"), Procedure:="ScheduleUpdate", Schedule:=status
    Application.OnTime EarliestTime:=TimeValue("4:45 PM"), Procedure:="ScheduleUpdate", Schedule:=status
End Sub
This code has to be scheduled to function at these specific times. So by checking the box and leaving it checked, the code should work at the appropriate times; however, if the box is unchecked it should stop. The code is there, but it does not seem to work...

IDEAS?!?!?!?!?! Thank you

I have a spreadsheet which calculates times that a vehicle takes to travel
from one destination to another at a variable speed input.

I need to be able to round-up the times to the nearest 15 minutes, ie; if
the calculation shows an arrival time of 11:34, I need to round this to
11:45.

Any ideas?

I have a spreadsheet with two columns that contain times (not dates,
just times) in military time format.

I want to make a third column that contains the hours and minutes
between the two times.

The first time is on a given day, and the next time is always on the
next day.

Some examples (with what should be returned):

Time 1 Time 2 Result
10:45 11:45 25:00
13:00 11:45 22:45
11:00 10:00 23:00

I know Excel can't display times greater than 23:59, so I have been
trying to use a formula to determine the hour, including a &":"&, and
then a formula for the minutes.

Anyone have any ideas? I keep getting close but then some instance
throws me!

--
Jaycatt
------------------------------------------------------------------------
Jaycatt's Profile: http://www.excelforum.com/member.php...o&userid=14633
View this thread: http://www.excelforum.com/showthread...hreadid=569565

We have discovered a problem in referencing time using the LOOKUP
function and I haven't been able to resolve it using VLOOKUP or the
INDEX and MATCH combination

The easiest way to demonstrate the problem is to creat a list of times
from 10:00 am to 12:00 pm in 15 minute increments, then copy the list
into the adjacent column and convert that list into the decimal
equivalent. See below for what the list should look like. I expanded
the decimals to the maximum number of decimals.

10:00 0.416666666666667
10:15 0.427083333333333
10:30 0.437500000000000
10:45 0.447916666666667
11:00 0.458333333333333
11:15 0.468750000000000
11:30 0.479166666666667
11:45 0.489583333333333
12:00 0.500000000000000

Now if you write a formula using LOOKUP or VLOOKUP to try to return the
decimal equivalent of a time in the left column. It works fine except
at a few times like 10:45 and 11:30 when it returns the values for
10:30 and 11:15 respectively.

Any help would be appreciated.

Jeff

--
jjhmbh
------------------------------------------------------------------------
jjhmbh's Profile: http://www.excelforum.com/member.php...o&userid=29748
View this thread: http://www.excelforum.com/showthread...hreadid=494626

I have 2 problems in trying to create difference variables representing
elapsed time between parts of a process:

1. If, say, an appt. was scheduled for 12:10, but the arrival time is
earlier, say, 11:45, Excel (the way I'm calculating it anyway:
Arrival-Schedule+IF(Schedule>Arrival,1) doesn't seem to be able to account
for the negative result -- (if I'm expressing the concept correctly); and

2. If there are overlapping times between parts of the process, so
UnitAdmission=2:30, but DocAssessFinish=3:45, again, I think it's an issue of
not being able to tell Excel how to calculate this. Normally the Admission
would take place AFTER the DocAssessFinish, so the formula would be:
UnitAdmission-DocAssessFinish, etc...

Help!

Thanks,

Meredith

Hi! I am so frustrated with the time formats on excel. I think they
make setting up functional worksheets so much more difficult. Enough
of the rant - onto the questions.

First, although I searched the forum for similar issues, I could not
understand how to solve my problem with the time format. I am making a
list of times: 15 minutes, 30 minutes, 45 minutes, and 60 minutes. I
formatted them as mm:ss and typed in 15:00 and the cells showed 00:00.
Is there anyway to get around this?

The reason I'm creating the list is because I'm trying to figure out a
way to categorize certain data. Here's what my spreadsheet *might*
look like...
A.......B..............C..................D....... ..........E....................................... ...........F
date..name..appointment time..arrival time..difference between Appt and
Arrival...on time/late appointment

11/17/04..Jones..10:00..10:05...5:00...On time
11/17/04..Anderson..11:00..11:45..45:00..30-45 minutes late
11/17/04..Smith..11:30..NA..NA..No show

The "DIFFERENCE" column is calculated for difference in minutes. I
want the on "time/late" column to either self populate with an if
statement or select from a list, using vlookup.

I tried an if statement using fifteen minute intervals, and couldn't
get it to work. For example, it may have looked like this:
=if(E2="NA","No show", if(E2>00:60, "More than 60 minutes late",
if(E2>00:45.......

Now, I'm sure this is incorrect, but I am not sure how to fix it. Is
there an easy way to do what I want to do with VLOOKUP? Or, am I
perhaps misunderstanding the IF function?

Any help would be greatly appreciated!!

Thanks in advance,
Marianne

--
MarianneR
------------------------------------------------------------------------
MarianneR's Profile: http://www.excelforum.com/member.php...fo&userid=6253
View this thread: http://www.excelforum.com/showthread...hreadid=314719

I need help in summing up time . Guy A worked for 11:45 in day 1 , then worked for 10:50 in day 2. How can sum up guy A's total worked hours for 2 days. the sum should be 22:35.
Please help

Just add them up:

=A1+A2
or
=sum(a1:a2)

And a custom format of:
[hh]:mm

will show the total hours/minutes.

egamalaki wrote:
>
> I need help in summing up time . Guy A worked for 11:45 in day 1 , then
> worked for 10:50 in day 2. How can sum up guy A's total worked hours
> for 2 days. the sum should be 22:35.
> Please help
>
> --
> egamalaki
> ------------------------------------------------------------------------
> egamalaki's Profile: http://www.excelforum.com/member.php...o&userid=23858
> View this thread: http://www.excelforum.com/showthread...hreadid=375020

--

Dave Peterson

QUESTION: I have a spreadsheet including all of my in and out times for work, leave, and lunch. If I leave in the morning for an appointment that runs through my lunch period, what formula can I use to calculate the time away from the office MINUS the lunch period or said another way, how can I get Excel to only count leave time that does not fall within the range representing my 45 minute lunch time?

I often have medical appointments during the day that I use either sick or annual leave for. I try to make my appointments overlap with my lunch time in order to reduce the amount of leave I must claim. My employer counts time in whole hour increments only, so we must keep our own running totals for partial hours used until it equals a whole hour. I'm able to handle the increments by rounding down to the whole hour interger and adding the remainder to a running total. Other details:

For example I arrive for work at 7:00 AM, leave for an appointment at 10:30 AM (begin sick leave calculation), my lunch start is 11:00 AM and lunch end is 11:45 AM, and I return from my appointment at 1:00 PM. I want B11 (below) to show 1hr 45 min or 1.75 instead of 2 hrs 30 min or 2.50.

-All time cells are formatted with h:mm AM/PM
-"Number hours used" cells are formatted as numbers with 2 decimal places
-"Number hours claimed" cells are formatted as number with no decimal places
-Current formulas in speadsheet are show in brackets [ ]

B2 - Arrival Time [user entry]
B3 - Lunch Start [user entry]
B4 - Lunch End [=SUM(B3+0.0315)]
B5 - Sick Leave (S/L) Start [user entry or empty]
B6 - S/L End [user entry or empty]
B7 - Annual Leave (A/L) [user entry or empty]
B8 - A/L End [user entry or empty]
B9 - Departure [user entry]
B10 - Number hours worked [=SUM((B9-B2)*24)-0.75
B11 - Number S/L hours used ?????
B12 - Number of A/L hours used ?????
B13 - Number of work hours claimed [=INT(B10)-(B14+B15)
B14 - Number S/L hours claimed [=INT (B11)]
B15 - Number A/L hous claimed [=INT(B12)]

I truly appreciate any help. I don't think the answer is difficult, just beyond my mental reach.

We have discovered a problem in referencing time using the LOOKUP function and I haven't been able to resolve it using VLOOKUP or the INDEX and MATCH combination

The easiest way to demonstrate the problem is to creat a list of times from 10:00 am to 12:00 pm in 15 minute increments, then copy the list into the adjacent column and convert that list into the decimal equivalent. See below for what the list should look like. I expanded the decimals to the maximum number of decimals.

10:00 0.416666666666667
10:15 0.427083333333333
10:30 0.437500000000000
10:45 0.447916666666667
11:00 0.458333333333333
11:15 0.468750000000000
11:30 0.479166666666667
11:45 0.489583333333333
12:00 0.500000000000000

Now if you write a formula using LOOKUP or VLOOKUP to try to return the decimal equivalent of a time in the left column. It works fine except at a few times like 10:45 and 11:30 when it returns the values for 10:30 and 11:15 respectively.

Any help would be appreciated.

Jeff

Hi everyone!

can someone help me correct the code which was given to me by one of our kind moderators...the problem arise when getting the time difference specially when the time strikes at 12 midnnight....Like if the inputed start time date is 09/23/2009 11:45 PM and the end time and date is 09/24/2009 12:15 AM...it outputs and incorrect value...user was event restricted to enter the end time because of this issue...

the code below i have is:
Sub Endb3()
    
   Dim BreakTime As Integer
   Dim dTimeS As Date ' start time
   Dim dTimeE As Date ' end time
   Dim dTimeL As Date ' time left
   Dim dTimeR As Date ' time returned

   If CurRow < 1 Then
      MsgBox "Please enter a valid Employee ID", vbExclamation
      Exit Sub
   End If
   BreakTime = 30     'Break Time in minutes
   dTimeS = Cells(CurRow, "L").Value
   dTimeE = dTimeS + TimeSerial(0, BreakTime, 0)
   With Me
      Select Case Time
      Case Is < dTimeE
         dTimeL = dTimeE - Time
         dTimeR = (dTimeE - TimeSerial(0, 5, 0))
         If Time < dTimeR Then
            'dTimeR = dTimeR - Time
            'MsgBox "You must wait at least " & Minute(dTimeR) _
               & " minutes and " & Second(dTimeR) _
               & " seconds before returning", vbInformation
            MsgBox "You are attempting to logout early. Please logout after " & Format(dTimeR, "hh:mm
AM/PM"), vbInformation, "Early LogOut NOT PERMITTED"
            Cells(CurRow, "M") = ""
            TextBox1.Value = vbNullString
            TextBox1.SetFocus
            lblemployee.Caption = ""
            lblshift.Caption = ""
            lblcoach.Caption = ""
            TextBox2.Text = ""
            lblinout.Caption = ""
            lblmsgbox.Caption = ""
            ListBox1.RowSource = vbNullString
            Exit Sub
         Else
            lblmsgbox.Caption = "You still have " & Minute(dTimeL) _
               & " minutes and " & Second(dTimeL) _
               & " seconds remaining. "
         End If
      Case Is > dTimeE
         dTimeL = Time - dTimeE
         lblmsgbox.Caption = "You are " & Minute(dTimeL) _
            & " minutes and " & Second(dTimeL) _
            & " second overbreak. "
                         
         Cells(CurRow, "M") = Time
      End Select
      Cells(CurRow, "M") = Time
      Application.Wait (Time() + CDate("00:00:01"))
      ActiveWorkbook.save
      
      TextBox1.Value = vbNullString
      TextBox1.SetFocus
      lblemployee.Caption = ""
      lblshift.Caption = ""
      lblcoach.Caption = ""
      TextBox2.Text = ""
      lblinout.Caption = ""
      lblmsgbox.Caption = ""
      ListBox1.RowSource = vbNullString
   End With
End Sub
I appreciate your continous help...

Regards,

Stoey

How do I write a routine (xl97) to fill a combo box on a userform with
the time starting at 12:00 AM and ending at 11:45 PM?

I populated a combo box from the times on a worksheet and wrote this. It
works. (cells A1 thru A96 have 12:00 AM, 12:15 AM, etc...)

Sub build_combobox1()

Dim x As Date

With Worksheets("Sheet3")
For x = 1 To 96
ComboBox1.AddItem .Cells(x, 1)
Next x
End With

End Sub

But I rather build the data instead of getting it. Something like this
which does not work. (the value of x I can't figure out)

Sub build_combobox1()

Dim x As Date

For x = 1200 To 2400 Step 15
ComboBox1.AddItem Format(x, "hh:mm AMPM")

Next x

End Sub

Here's my normal shifts. how would i be able to add those times together to get the hours that i've worked, including my lunch, which is an hour.

06:00:00 AM
11:45:00 AM
12:45:00 PM
05:04:00 PM


No luck finding an answer? You could always try Google.