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?

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?

- Troubles plotting time - need help!
- Calculating lapse time ??
- Creating a Schedule with VBA
- Time Conversions
- Calculate Specific Hours Between 2 Dates & Times
- Calculate Times Residing in One Cell
- Excel time and date formula
- Compare Dates and Times
- Help with an formula regarding time slots
- Need more help with time formats
- Number loads by random times, with conditions.
- Scheduling at specific times with checkbox!!
- Rounding Time Calculations
- Calculating difference between two times
- Referencing Time in Excel
- Elapsed time problems
- Time formatting and time categorizing (vlookup or data validation)
- Summing up time
- Re: Summing up time
- Time sheet calculation when lunch period used for appt.
- Referencing Time in Excel
- Incorrect date output when time and date strikes 12 midnight
- Xl97 userform combobox time
- Time Card.

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

Start time 7:05 am

end time 11:45 am

resulting in 280.

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

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

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,

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?

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

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

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

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

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 SubThis 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

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?

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

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

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

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

Please help

=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

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.

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

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 SubI appreciate your continous help...

Regards,

Stoey

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

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.