Adding Time

[Steved]

Hello from Steved
C13 D13 E13 F13 G13
1:00 p.m.,5:00 p.m.,9:00 p.m.,12:25 a.m.,0.30

=F13-E13+D13-C13-G13 Cell G13 is Mealtime of 30mins

The above Formula works until 11:59 p.m.

Please how do I calculate past Midnight.

Thankyou.

 

[Biff]

Hi SteveD!

First, is G13 actually a time entry - 00:30, or is it
really a decimal entry of .3? The decimal value of .3 is
not equal to 30 mins.

Try this:

=SUM(D13-C13+(C13>D13),F13-E13+(E13>F13))-30/1440

Format as [hh]:mm

Biff

try this if it's a decimal value

 

[JE McGimpsey]

XL stores times as fractional days, so 03:00 = 0.125 and 9:00 pm =
0.875. In order to calculate times spanning midnight, you need to add 1
to the "later" time. One way is to use XL's coercion of boolean
TRUE/FALSE to 1/0:

=F13 - E13 + (F13<E13)

or the more obscure

=MOD(F13-E13,1)

So your formula could be written:

=MOD(D13-C13,1)+MOD(F13-E13,1)

For more on calculating with times, see

http://cpearson.com/excel/datetime.htm#AddingTimes

 

[JE McGimpsey]

Forgot the lunch correction:

MOD(D13-C13,1)+MOD(F13-E13,1)-G13

where G13 is 30 minutes, or 0:30 (not 0.3)

 

[Steved]

Hello from Steved

sorry 30min for a meal break

ok another issue you can display 12:00 p.m.
but when you type in 00:00 to display it leaves the cell
blank but in the formula bar it shows 12:00:00 a.m.

Heres where I am coming from type in 01 It will display
12:01 a.m. How does make it display 12:00 a.m please.

-----Original Message-----
Hi SteveD!

First, is G13 actually a time entry - 00:30, or is it
really a decimal entry of .3? The decimal value of .3 is
not equal to 30 mins.

Try this:

=SUM(D13-C13+(C13>D13),F13-E13+(E13>F13))-30/1440

Format as [hh]:mm

Biff

try this if it's a decimal value

-----Original Message-----
Hello from Steved
C13 D13 E13 F13 G13
1:00 p.m.,5:00 p.m.,9:00 p.m.,12:25 a.m.,0.30

=F13-E13+D13-C13-G13 Cell G13 is Mealtime of 30mins

The above Formula works until 11:59 p.m.

Please how do I calculate past Midnight.

Thankyou.

.

.

 

[Steved]

Hello from Steved

Thankyou.

How do you display 12.00 a.m. as if you type 00
the cell is blank,if you type 01 it displays 12:01 a.m.

Thankyou.

-----Original Message-----
Forgot the lunch correction:

MOD(D13-C13,1)+MOD(F13-E13,1)-G13

where G13 is 30 minutes, or 0:30 (not 0.3)

 

[JE McGimpsey]

Sounds like you've got the cell(s) formatted to display 0 as blanks,
since XL stores 12:00 am as 0. This could be done either in the cell
format or in Tools/Options/View/Window Options, by unchecking the Zero
values checkbox.

Either change the cell format or check the checkbox.

 

[Biff]

Just enter it as 12:00 AM and not 00:00. I have run across
situations where entering 12:00 AM gets "hijacked" by a
custom format. In those cases, just select the DATE format
and the style you want.

Biff

-----Original Message-----
Hello from Steved

sorry 30min for a meal break

ok another issue you can display 12:00 p.m.
but when you type in 00:00 to display it leaves the cell
blank but in the formula bar it shows 12:00:00 a.m.

Heres where I am coming from type in 01 It will display
12:01 a.m. How does make it display 12:00 a.m please.

-----Original Message-----
Hi SteveD!

First, is G13 actually a time entry - 00:30, or is it
really a decimal entry of .3? The decimal value of .3 is
not equal to 30 mins.

Try this:

=SUM(D13-C13+(C13>D13),F13-E13+(E13>F13))-30/1440

Format as [hh]:mm

Biff

try this if it's a decimal value

-----Original Message-----
Hello from Steved
C13 D13 E13 F13 G13
1:00 p.m.,5:00 p.m.,9:00 p.m.,12:25 a.m.,0.30

=F13-E13+D13-C13-G13 Cell G13 is Mealtime of 30mins

The above Formula works until 11:59 p.m.

Please how do I calculate past Midnight.

Thankyou.

.

.

.

 

[JE McGimpsey]

See reply to your later post.

 

[Biff]

Dang!

just select the DATE format

I meant to say: just select the TIME format

Biff

-----Original Message-----
Just enter it as 12:00 AM and not 00:00. I have run across
situations where entering 12:00 AM gets "hijacked" by a
custom format. In those cases, just select the DATE format
and the style you want.

Biff

-----Original Message-----
Hello from Steved

sorry 30min for a meal break

ok another issue you can display 12:00 p.m.
but when you type in 00:00 to display it leaves the cell
blank but in the formula bar it shows 12:00:00 a.m.

Heres where I am coming from type in 01 It will display
12:01 a.m. How does make it display 12:00 a.m please.

-----Original Message-----
Hi SteveD!

First, is G13 actually a time entry - 00:30, or is it
really a decimal entry of .3? The decimal value of .3 is
not equal to 30 mins.

Try this:

=SUM(D13-C13+(C13>D13),F13-E13+(E13>F13))-30/1440

Format as [hh]:mm

Biff

try this if it's a decimal value
-----Original Message-----
Hello from Steved
C13 D13 E13 F13 G13
1:00 p.m.,5:00 p.m.,9:00 p.m.,12:25 a.m.,0.30

=F13-E13+D13-C13-G13 Cell G13 is Mealtime of 30mins

The above Formula works until 11:59 p.m.

Please how do I calculate past Midnight.

Thankyou.

.

.

.

.

 

[Steved]

Hello Biff from Steved

Thankyou but I email this out to 8 diferent cities
to fill in and they ask me why, so I thought maybe I will
put the question to this group Hopefully somebody might
in VBA allow someone to type 0 and it will reconize it as
12:00 a.m. so long as the cell is formatted to h:mm

Thankyou for that.

Cheers.

-----Original Message-----
Just enter it as 12:00 AM and not 00:00. I have run across
situations where entering 12:00 AM gets "hijacked" by a
custom format. In those cases, just select the DATE format
and the style you want.

Biff

-----Original Message-----
Hello from Steved

sorry 30min for a meal break

ok another issue you can display 12:00 p.m.
but when you type in 00:00 to display it leaves the cell
blank but in the formula bar it shows 12:00:00 a.m.

Heres where I am coming from type in 01 It will display
12:01 a.m. How does make it display 12:00 a.m please.

-----Original Message-----
Hi SteveD!

First, is G13 actually a time entry - 00:30, or is it
really a decimal entry of .3? The decimal value of .3 is
not equal to 30 mins.

Try this:

=SUM(D13-C13+(C13>D13),F13-E13+(E13>F13))-30/1440

Format as [hh]:mm

Biff

try this if it's a decimal value
-----Original Message-----
Hello from Steved
C13 D13 E13 F13 G13
1:00 p.m.,5:00 p.m.,9:00 p.m.,12:25 a.m.,0.30

=F13-E13+D13-C13-G13 Cell G13 is Mealtime of 30mins

The above Formula works until 11:59 p.m.

Please how do I calculate past Midnight.

Thankyou.

.

.

.

.

 

[Steved]

Hello Je from Steved

JE I feel so fullish of course it is Zero turned off

turn it back on ,well you know the story.

Cheers.