# Hours between 2 intervals

1. ## Hours between 2 intervals

I need a way to calculate hours between two intervals. Example 3:00pm and 1:00am should yield 10. I'm using this to determine shift length for a day, then adding up multiple days to get hours scheduled in a week.

Currently I'm using "=b1-a1+(b1<a1)*24" where a1 is 3:00pm and b1 is 1:00am. This formula does yield the accurate result; however, when I attempt to add up multiple formulas like this to get a weekly total, my weekly total becomes astronomically high, and I have no idea why. I also always run into an issue where this becomes more difficult to calculate if the shift traverses midnight.

2. ## Re: Hours between 2 intervals

If A1 and B1 contain Excel times of the form 12:34, the formula should be:

"=( b1-a1+(b1<=a1) )*24

The important thing is the addition of the outer parentheses.

Changing "<" to "<=" is an improvement. It ensures that when A1 and B1 appear to be the same time, their difference is interpreted as 24 hours, not zero.

We cannot say with impunity that will fix all of your problems because you neglected to provide specifics, namely a set up of pairs of numbers and the result that you get when you add them that is "astronomical".

3. ## Re: Hours between 2 intervals

Hey joeu2004,

My apologies for not clarifying. I've attached a file with an example of the issue I've ran into. Cells S2:Y2 are calculating the hours worked correctly; however, cell Z2, is yielding 116 hours, when the schedule shows the agent working 7.5 hours 5 days in the week.

Hopefully this helps clarify. Let me know if you need something else.

4. ## Re: Hours between 2 intervals

First, since you multiply the Excel time arithmetic by 24, you are calculating decimal hours.

So the format of S2:Y2 should be Number, just like Z2 and AA2.

Second, you should apply the correction that I suggested previously.

The formulas should be of the form:

=IFERROR( ( F2-E2+(F2 <= E2) )*24,"")

Then everything will be copacetic.

5. ## Re: Hours between 2 intervals

Thank you! This worked great!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.93017 seconds
• Memory Usage 8,982KB
• Queries Executed 16 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (5)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (5)postbit_legacy
• (5)postbit_onlinestatus
• (5)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (50):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• postbit_attachment
• tag_fetchbit
• tag_fetchbit_complete