Adding hours and minutes

P

Peter

I am trying to add a column of times, e.g.6:34 + 8:44, all is fine until the
total exceeds 24 hours when it becomes a random(?) date followed by hours
and minutes, (12/5/04 8:30). How should I format the total cell to show
just hours:minutes.

Peter
 
P

Peter

Thanks that's sorted - now my job sheet truly reflects the hours I've
worked.

Peter

DDM said:
Peter, like this: [h]:mm.
--
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com


Peter said:
I am trying to add a column of times, e.g.6:34 + 8:44, all is fine until the
total exceeds 24 hours when it becomes a random(?) date followed by hours
and minutes, (12/5/04 8:30). How should I format the total cell to show
just hours:minutes.

Peter
Click to expand...
Click to expand...
 
H

Hans Erkamp

Hi

Please help

Which formule can I use to get the next day in B1

For example:
A1 = Monday
B1 = formule A1 + 1 = Tuesday

A1 = Wednesday
B1 = formule A1 + 1 = Thursday

A B
1 Monday (A1+1)
2
3
4

Regards

Hans
 
H

Hans Erkamp

Hi

Please help

Which formule can I use to get the next day in B1

For example:
A1 = Monday
B1 = formule A1 + 1 = Tuesday

A1 = Wednesday
B1 = formule A1 + 1 = Thursday

A B
1 Monday (A1+1)
2
3
4

Regards

Hans
 
D

DDM

Hans, enter a date in Cell A1, then right-click it and Format Cells > Number
Custom. In the Type: field, enter dddd, then OK. The date in A1 will
Click to expand...
display as, say, Monday. In A2, enter =A1+1.
 
H

Hans Erkamp

Peo,
have tried your advice but it is not working (yet) hi hi
Works well with now() in A1 (tuesday) and than in B1 (A1+1) gives
wednesday, OK so far.
But I like the type a day of the week(tuesday) in cel A1 with the result
that in B1 will come wednesday.

Now I am looking in your
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com

I see a lot

But still a have the same question hi hi hi

Thanks.

Hans
 
A

Anders S

I post in both threads in microsoft.public.excel.

Hans,

Are you saying that if you *type* "Monday" in A1, you want "Tuesday" in B1?

Regards
Anders Silven
 
H

Hans Erkamp

Hallo Anders,
YES

regards
Hans



Anders said:
I post in both threads in microsoft.public.excel.

Hans,

Are you saying that if you *type* "Monday" in A1, you want "Tuesday" in B1?

Regards
Anders Silven
Click to expand...
 
H

Hans Erkamp

GM Anders

do you have a formula ?
Regards
Hans


Anders said:
I post in both threads in microsoft.public.excel.

Hans,

Are you saying that if you *type* "Monday" in A1, you want "Tuesday" in B1?

Regards
Anders Silven
Click to expand...
 
W

Wouter

Hello Hans,

This strongly depends on the way how you filled A1.
If you typed 'Monday' as text I do not see an option with one of the
standasrd functions.

In you used =WEEKDAY(AnOtherCell) then use for B1 =WEEKDAY(AnOtherCell
+ 1)
If you used a specific day as value and 'dddd' as celformat
simply use =A1 + 1 for B1.

You can use an UserDefinedFunction is you use text for A1:

Public Function NextWeekDay(CurrentDay As String, interval As Integer)
As String
Dim intHulp As Integer
'
Select Case UCase(CurrentDay)
Case "MONDAY"
intHulp = 1 + interval
Case "TUESDAY"
intHulp = 2 + interval
Case "WEDNESDAY"
intHulp = 3 + interval
Case "THURSDAY"
intHulp = 4 + interval
Case "FRYDAY"
intHulp = 5 + interval
Case "SATURDAY"
intHulp = 6 + interval
Case "SUNDAY"
intHulp = 7 + interval
End Select
While intHulp > 7
intHulp = intHulp - 7
Wend
While intHulp < 1
intHulp = intHulp - 1
Wend
Select Case intHulp
Case 1
NextWeekDay = "Monday"
Case 2
NextWeekDay = "Tuesday"
Case 3
NextWeekDay = "Wednesday"
Case 4
NextWeekDay = "Thursday"
Case 5
NextWeekDay = "Fryday"
Case 6
NextWeekDay = "Saturday"
Case 7
NextWeekDay = "Sunday"
End Select
End Function

For B1 you get the function =NextWeekDay(A1, 1)

Succes,

Wouter HM.
 
H

Hans Erkamp

Hi Wouter,
ik ga aan de slag.
Bedankt.
Hans

Hello Hans,

This strongly depends on the way how you filled A1.
If you typed 'Monday' as text I do not see an option with one of the
standasrd functions.

In you used =WEEKDAY(AnOtherCell) then use for B1 =WEEKDAY(AnOtherCell
+ 1)
If you used a specific day as value and 'dddd' as celformat
simply use =A1 + 1 for B1.

You can use an UserDefinedFunction is you use text for A1:

Public Function NextWeekDay(CurrentDay As String, interval As Integer)
As String
Dim intHulp As Integer
'
Select Case UCase(CurrentDay)
Case "MONDAY"
intHulp = 1 + interval
Case "TUESDAY"
intHulp = 2 + interval
Case "WEDNESDAY"
intHulp = 3 + interval
Case "THURSDAY"
intHulp = 4 + interval
Case "FRYDAY"
intHulp = 5 + interval
Case "SATURDAY"
intHulp = 6 + interval
Case "SUNDAY"
intHulp = 7 + interval
End Select
While intHulp > 7
intHulp = intHulp - 7
Wend
While intHulp < 1
intHulp = intHulp - 1
Wend
Select Case intHulp
Case 1
NextWeekDay = "Monday"
Case 2
NextWeekDay = "Tuesday"
Case 3
NextWeekDay = "Wednesday"
Case 4
NextWeekDay = "Thursday"
Case 5
NextWeekDay = "Fryday"
Case 6
NextWeekDay = "Saturday"
Case 7
NextWeekDay = "Sunday"
End Select
End Function

For B1 you get the function =NextWeekDay(A1, 1)

Succes,

Wouter HM.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count days hours and minutes 13
WCG Stats Sunday 23 January 2022 1
WCG Stats Saturday 13 November 2021 1
Subtracting hours and minutes with negative times 2
WCG Stats Monday 17 January 2022 1
WCG Stats Tuesday 23 November 2021 1
WCG Stats Wednesday 26 January 2022 1
WCG Stats Wednesday 01 December 2021 1

Top