Armed Polite Society

Main Forums => The Roundtable => Topic started by: Brad Johnson on March 18, 2018, 07:16:17 PM

Title: Excel function question
Post by: Brad Johnson on March 18, 2018, 07:16:17 PM

Hive Mind...

I'm setting up a scheduling worksheet in Excel. I'm done except for one function. I have a daily hour total derived with the formula =TIME(CELLB-CELLY, "h:mm"). I want to calculate daily total hours by taking that number less a single numerical value used to represent lunches.

I would have though it was simple as +derivedvaluecell-lunchvaluecell. Nope. There's obviously a step I'm missing to have Excel treat the daily hour total as a single number for purpose of the net daily time calc, but I can't for the life of me figure out what it is.

Thoughts?

Brad

Title: Re: Excel function question
Post by: Brad Johnson on March 18, 2018, 07:29:35 PM

Never mind. SWMBO figured it out. I needed to insert the lunch hour value in 0:00 format rather than as simple numeric value.

Nothing to see here. Move along please.

Brad

Title: Re: Excel function question
Post by: Jamisjockey on March 18, 2018, 07:34:21 PM

You have exceltile dysfunction?

Title: Re: Excel function question
Post by: Brad Johnson on March 18, 2018, 08:40:43 PM

Premature concatination.

Brad

Title: Re: Excel function question
Post by: Doggy Daddy on March 19, 2018, 08:41:28 AM

Quote from: Brad Johnson on March 18, 2018, 07:29:35 PM

Never mind. SWMBO figured it out. I needed to insert the lunch hour value in 0:00 format rather than as simple numeric value.

Nothing to see here. Move along please.

Brad

format the time values as custom [hh]:mm.  Also, be aware that if at any point the time value goes negative, you'll get railroad tracks.