Support NeoGAF

[BigJonsson]

I need some help

Lets say I have a spreadsheet set-up to use as a schedule for employees at work, so I have there name and then the shift times in the cells going across
How can I get excel to calculate the total hours they are to work for the week?

 

[Ecrofirt]

can you post a uick example of what it looks like?

 

[Jewbacca]

Create hidden cells...

Like for example


Column B would have 4:30 - 10:30 Pm column C would be hidden and say like 6:00

and just hide column C... that way you can make a formula at the end with the hidden columns and keep it professional looking.

 

[BigJonsson]

Create hidden cells...

Like for example


Column B would have 4:30 - 10:30 Pm column C would be hidden and say like 6:00

and just hide column C... that way you can make a formula at the end with the hidden columns and keep it professional looking.

How do you create hidden cells lol

And yea, what you described sounds right

But can I make a forumala that calculates the time for me?

 

[Ecrofirt]

If you would post a good sample of what you actually have it looknig like, I might *gasp* be able to help.

 

[BigJonsson]

haha

I'll e-mail myself a copy of it tomorrow :s

 

[Mejilan]

I did the electronic timesheets for the company I was working for about 2 years ago, in Excel.

All you had to do was input your Ins and Outs (2 per day, morning in, lunch out, lunch in, evening out), and boom, it'd calculate your daily total, weekly total, etc. If you were out, all you had to do was plop in a 3 digit code (for bereavement, jury duty, vacation day, personal day, company holiday, etc), and it'd take those into account as well. Hell, it'd even calculate your OT. Man, I wish I still had a copy of that, it was the best Excel lesson I ever had. Figured out so much shit the week I worked on that, fine tuning it and whatnot.

As for hiding. Just select the entire column, right click, and choose HIDE, and boom, it's hidden. (After finishing with the formulae, of course.)

 

[BigJonsson]

hmmm

so if i can figure out a way that it will automatically output the length of the shift to a hidden column then i'm all good

 

[Mejilan]

Whaddya mean?

 

[BigJonsson]

Ok let say I have this

Name monday
person x 11:00-6:30

now lets say i have that going across the spreadsheet, i want at the end of the row it to output how many hours total that person is scheduled for

 

[GhaleonEB]

I found two functions in Exel that convert time to numerical values which can be manipulated as numbers. I'm still trying to get one of them to do what you want it to, but I thought I'd toss it out there while I'm tinkering.

TIME

Returns the decimal number for a particular time. The decimal number returned by TIME is a value ranging from 0 to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.).

Syntax

TIME(hour,minute,second)

Hour is a number from 0 (zero) to 23 representing the hour.

Minute is a number from 0 to 59 representing the minute.

Second is a number from 0 to 59 representing the second.

Remark

Microsoft Excel for Windows and Microsoft Excel for the Macintosh use different date systems as their default. Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day). Learn more about how Microsoft Excel stores dates and times.

Examples

TIME(12, 0, 0) equals the serial number 0.5, which is equivalent to 12:00:00 P.M.

TIME(16, 48, 10) equals the serial number 0.700115741, which is equivalent to 4:48:10 P.M.

TEXT(TIME(23, 18, 14), "h:mm:ss AM/PM") equals "11:18:14 PM"

TIMEVALUE
See Also

Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.).

Syntax

TIMEVALUE(time_text)

Time_text is a text string that represents a time in any one of the Microsoft Excel time formats. For example, "6:45 PM" and "18:45" text strings within quotation marks that represent time. Date information in time_text is ignored.

Remark

Microsoft Excel for Windows and Microsoft Excel for the Macintosh use different date systems as their default. Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day). Learn more about how Microsoft Excel stores dates and times.

Examples

TIMEVALUE("2:24 AM") equals 0.1

TIMEVALUE("22-Aug-1955 6:35 AM") equals 0.274305556