PLEASE IGNORE THE PREVIOUS MESSAGE POSTED BY MYSELF!

I am currently attempting to work out the flexi time earned and taken in a
week. The problem i am having is that if some one is taking flexi they would
have had to work more than 37:00 hours a week, so if they haven't the flexi
is a minus time. I need help with adding to times togeather in the format
[h]:mm only if two conditions are met which are that an "F" needs to placed
in a cell to confirm flexi being taken and one of the cells that need to be
added must be greater the 0:00. If flexi has been earned by working over
37:00 hours and not taken then it must role over to the following week, as
must the flexi hours that have not been earned (i.e less than 37:00 hours
worked, so the
flexi is a minus time) also ned to be rolled over, so that the employee can
make up the time. Than i need to clear the cell at the end of the week
because if an employee has taken the the flexi then the cell needs to be
cleared, also if the flexi is not taken due to not having any flexi to take
(worked less than 37:00 hours) or out of personal choice than the flexi rolls
over either way as i have explained above, so again the cell will need to be
cleared, SO what ever happens with the flexi i will need to clear the cell
with some code in the final part of the formulae i am having trouble with.

These are the attempts that i have made with no luck;

=IF(AND(E10="F",H400),H36+H40,H40="")

=IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm"))

=IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm"))

Any guidence would be greatly appreciated.

the problem is the 37:00 is not really 37:00 but is storesd as 1.54 which is
the number of days. If you format the cell as a number instead of the
date/time you wil see this number. To get back to the number of hours use

= A1 * 24 (make sure the cell is formated a number and not time).

To get minutes, seconds, and hours

=60 * mod(A1 * 24,1) <=this is the number of minutes, fraction times 60
= 60 * (60 * mod(A1 * 24,1)) <= this is seconds
= int(A1) <=hours, whole number


"Zaf" wrote:

PLEASE IGNORE THE PREVIOUS MESSAGE POSTED BY MYSELF!

I am currently attempting to work out the flexi time earned and taken in a
week. The problem i am having is that if some one is taking flexi they would
have had to work more than 37:00 hours a week, so if they haven't the flexi
is a minus time. I need help with adding to times togeather in the format
[h]:mm only if two conditions are met which are that an "F" needs to placed
in a cell to confirm flexi being taken and one of the cells that need to be
added must be greater the 0:00. If flexi has been earned by working over
37:00 hours and not taken then it must role over to the following week, as
must the flexi hours that have not been earned (i.e less than 37:00 hours
worked, so the
flexi is a minus time) also ned to be rolled over, so that the employee can
make up the time. Than i need to clear the cell at the end of the week
because if an employee has taken the the flexi then the cell needs to be
cleared, also if the flexi is not taken due to not having any flexi to take
(worked less than 37:00 hours) or out of personal choice than the flexi rolls
over either way as i have explained above, so again the cell will need to be
cleared, SO what ever happens with the flexi i will need to clear the cell
with some code in the final part of the formulae i am having trouble with.

These are the attempts that i have made with no luck;

=IF(AND(E10="F",H400),H36+H40,H40="")

=IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm"))

=IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm"))

Any guidence would be greatly appreciated.

Hi,

I appreciate the comments, the thing is that when istarted the the timesheet
was already produced, but he formulaes were missing, so i was instructed to
keep the formatting the way it was and to work aroud it. I have already have
had to find a way to show negative time; i have suggested changing the
format, but was told NO. I understand that if converted to number than it
will be displayed as 1.54, but i still i little fuzzy with your other
suggesstion. PLEASE if you have any other suggestions then could you let me
know.

THANK YOU

Kind Regards
Zaf

"Joel" wrote:

the problem is the 37:00 is not really 37:00 but is storesd as 1.54 which is
the number of days. If you format the cell as a number instead of the
date/time you wil see this number. To get back to the number of hours use

= A1 * 24 (make sure the cell is formated a number and not time).

To get minutes, seconds, and hours

=60 * mod(A1 * 24,1) <=this is the number of minutes, fraction times 60
= 60 * (60 * mod(A1 * 24,1)) <= this is seconds
= int(A1) <=hours, whole number


"Zaf" wrote:

PLEASE IGNORE THE PREVIOUS MESSAGE POSTED BY MYSELF!

I am currently attempting to work out the flexi time earned and taken in a
week. The problem i am having is that if some one is taking flexi they would
have had to work more than 37:00 hours a week, so if they haven't the flexi
is a minus time. I need help with adding to times togeather in the format
[h]:mm only if two conditions are met which are that an "F" needs to placed
in a cell to confirm flexi being taken and one of the cells that need to be
added must be greater the 0:00. If flexi has been earned by working over
37:00 hours and not taken then it must role over to the following week, as
must the flexi hours that have not been earned (i.e less than 37:00 hours
worked, so the
flexi is a minus time) also ned to be rolled over, so that the employee can
make up the time. Than i need to clear the cell at the end of the week
because if an employee has taken the the flexi then the cell needs to be
cleared, also if the flexi is not taken due to not having any flexi to take
(worked less than 37:00 hours) or out of personal choice than the flexi rolls
over either way as i have explained above, so again the cell will need to be
cleared, SO what ever happens with the flexi i will need to clear the cell
with some code in the final part of the formulae i am having trouble with.

These are the attempts that i have made with no luck;

=IF(AND(E10="F",H400),H36+H40,H40="")

=IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm"))

=IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm"))

Any guidence would be greatly appreciated.

My suggestions wher to seperate the days from the hours

Days = Int(1.54) = 1
Fraction of Days = Mod(1.54,1) = .54

Hours are
24 * .54 = 12.96
full hours = Int(12.96) = 12

fraction of hours = Mod(12.96,1) = .96

Minutes = .96 * 60 = 57.6

Full Minutes = Int(57.6) = 57

fraction of minute = Mod(57.6,1) = .6

Seconds = .6 * 60 = 36




"Zaf" wrote:

Hi,

I appreciate the comments, the thing is that when istarted the the timesheet
was already produced, but he formulaes were missing, so i was instructed to
keep the formatting the way it was and to work aroud it. I have already have
had to find a way to show negative time; i have suggested changing the
format, but was told NO. I understand that if converted to number than it
will be displayed as 1.54, but i still i little fuzzy with your other
suggesstion. PLEASE if you have any other suggestions then could you let me
know.

THANK YOU

Kind Regards
Zaf

"Joel" wrote:

the problem is the 37:00 is not really 37:00 but is storesd as 1.54 which is
the number of days. If you format the cell as a number instead of the
date/time you wil see this number. To get back to the number of hours use

= A1 * 24 (make sure the cell is formated a number and not time).

To get minutes, seconds, and hours

=60 * mod(A1 * 24,1) <=this is the number of minutes, fraction times 60
= 60 * (60 * mod(A1 * 24,1)) <= this is seconds
= int(A1) <=hours, whole number


"Zaf" wrote:

PLEASE IGNORE THE PREVIOUS MESSAGE POSTED BY MYSELF!

I am currently attempting to work out the flexi time earned and taken in a
week. The problem i am having is that if some one is taking flexi they would
have had to work more than 37:00 hours a week, so if they haven't the flexi
is a minus time. I need help with adding to times togeather in the format
[h]:mm only if two conditions are met which are that an "F" needs to placed
in a cell to confirm flexi being taken and one of the cells that need to be
added must be greater the 0:00. If flexi has been earned by working over
37:00 hours and not taken then it must role over to the following week, as
must the flexi hours that have not been earned (i.e less than 37:00 hours
worked, so the
flexi is a minus time) also ned to be rolled over, so that the employee can
make up the time. Than i need to clear the cell at the end of the week
because if an employee has taken the the flexi then the cell needs to be
cleared, also if the flexi is not taken due to not having any flexi to take
(worked less than 37:00 hours) or out of personal choice than the flexi rolls
over either way as i have explained above, so again the cell will need to be
cleared, SO what ever happens with the flexi i will need to clear the cell
with some code in the final part of the formulae i am having trouble with.

These are the attempts that i have made with no luck;

=IF(AND(E10="F",H400),H36+H40,H40="")

=IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm"))

=IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm"))

Any guidence would be greatly appreciated.

MORE HELP NEEDED PLEASE!

"Joel" wrote:

My suggestions wher to seperate the days from the hours

Days = Int(1.54) = 1
Fraction of Days = Mod(1.54,1) = .54

Hours are
24 * .54 = 12.96
full hours = Int(12.96) = 12

fraction of hours = Mod(12.96,1) = .96

Minutes = .96 * 60 = 57.6

Full Minutes = Int(57.6) = 57

fraction of minute = Mod(57.6,1) = .6

Seconds = .6 * 60 = 36




"Zaf" wrote:

Hi,

I appreciate the comments, the thing is that when istarted the the timesheet
was already produced, but he formulaes were missing, so i was instructed to
keep the formatting the way it was and to work aroud it. I have already have
had to find a way to show negative time; i have suggested changing the
format, but was told NO. I understand that if converted to number than it
will be displayed as 1.54, but i still i little fuzzy with your other
suggesstion. PLEASE if you have any other suggestions then could you let me
know.

THANK YOU

Kind Regards
Zaf

"Joel" wrote:

the problem is the 37:00 is not really 37:00 but is storesd as 1.54 which is
the number of days. If you format the cell as a number instead of the
date/time you wil see this number. To get back to the number of hours use

= A1 * 24 (make sure the cell is formated a number and not time).

To get minutes, seconds, and hours

=60 * mod(A1 * 24,1) <=this is the number of minutes, fraction times 60
= 60 * (60 * mod(A1 * 24,1)) <= this is seconds
= int(A1) <=hours, whole number


"Zaf" wrote:

PLEASE IGNORE THE PREVIOUS MESSAGE POSTED BY MYSELF!

I am currently attempting to work out the flexi time earned and taken in a
week. The problem i am having is that if some one is taking flexi they would
have had to work more than 37:00 hours a week, so if they haven't the flexi
is a minus time. I need help with adding to times togeather in the format
[h]:mm only if two conditions are met which are that an "F" needs to placed
in a cell to confirm flexi being taken and one of the cells that need to be
added must be greater the 0:00. If flexi has been earned by working over
37:00 hours and not taken then it must role over to the following week, as
must the flexi hours that have not been earned (i.e less than 37:00 hours
worked, so the
flexi is a minus time) also ned to be rolled over, so that the employee can
make up the time. Than i need to clear the cell at the end of the week
because if an employee has taken the the flexi then the cell needs to be
cleared, also if the flexi is not taken due to not having any flexi to take
(worked less than 37:00 hours) or out of personal choice than the flexi rolls
over either way as i have explained above, so again the cell will need to be
cleared, SO what ever happens with the flexi i will need to clear the cell
with some code in the final part of the formulae i am having trouble with.

These are the attempts that i have made with no luck;

=IF(AND(E10="F",H400),H36+H40,H40="")

=IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm"))

=IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm"))

Any guidence would be greatly appreciated.

I don't know you experience so lets start at the beginning.

time in hours start at midnight with 0 = midnight. A day equals 1, so ecry
hour is 1/24, and every minute = 1/(24)(60), every second = 1/(24)(60)(60)

so 8:00 AM = 8/24
8:00 PM = 20/24

8:30 AM = 8/24 + 30/(24)(60) = 1/3 + 1/48 = 16/48 + 1/48 = 17/48 =
..3541.6666

Now a Date starts at Jan 1, 1900 with every day = 1 (just like time, I
wonder why?).

July 8,2008 = 39737. Excel stores the date as a number and if you format
the cell to date the date will appear. If you enter 39737 in a cell then
change the format to date you will get todays date

July 8,2008 at 8:30 AM = 39737 + .3541666 = 39737.3541666


Your probelm results from a time in hours being greter that 24. Excel
usually assumes any time without a date is Jan 1, 1900 at midnight. If your
total time is 36 hours excel things the time is really Jan 2, 1900 at 12:00
PM. Because 36 is really 1.5 days excel add 1.5 to Jan 1, 1900 and gets Jan
2.

My suggestions was to seperate the days from the hours.

Using INT will get the whole number of days from the time

Days = Int(1.54) = 1

Using Mod will get the fraction part of the day.

Fraction of Days = Mod(1.54,1) = .54

Convert fraction of days to hours you multiply by 24

Hours are
24 * .54 = 12.96


Using Int will seperate the whole hours from the fraction

full hours = Int(12.96) = 12
You could also use the hour(.54)

Using MOD will get the fraction of the hours

fraction of hours = Mod(12.96,1) = .96

Minutes = .96 * 60 = 57.6

Full Minutes = Int(57.6) = 57
or simply =minute(.54)

fraction of minute = Mod(57.6,1) = .6

Seconds = .6 * 60 = 36
or simply =second(.54)



"Zaf" wrote:

Hi,

I appreciate the comments, the thing is that when istarted the the timesheet
was already produced, but he formulaes were missing, so i was instructed to
keep the formatting the way it was and to work aroud it. I have already have
had to find a way to show negative time; i have suggested changing the
format, but was told NO. I understand that if converted to number than it
will be displayed as 1.54, but i still i little fuzzy with your other
suggesstion. PLEASE if you have any other suggestions then could you let me
know.

THANK YOU

Kind Regards
Zaf

"Joel" wrote:

the problem is the 37:00 is not really 37:00 but is storesd as 1.54 which is
the number of days. If you format the cell as a number instead of the
date/time you wil see this number. To get back to the number of hours use

= A1 * 24 (make sure the cell is formated a number and not time).

To get minutes, seconds, and hours

=60 * mod(A1 * 24,1) <=this is the number of minutes, fraction times 60
= 60 * (60 * mod(A1 * 24,1)) <= this is seconds
= int(A1) <=hours, whole number


"Zaf" wrote:

PLEASE IGNORE THE PREVIOUS MESSAGE POSTED BY MYSELF!

I am currently attempting to work out the flexi time earned and taken in a
week. The problem i am having is that if some one is taking flexi they would
have had to work more than 37:00 hours a week, so if they haven't the flexi
is a minus time. I need help with adding to times togeather in the format
[h]:mm only if two conditions are met which are that an "F" needs to placed
in a cell to confirm flexi being taken and one of the cells that need to be
added must be greater the 0:00. If flexi has been earned by working over
37:00 hours and not taken then it must role over to the following week, as
must the flexi hours that have not been earned (i.e less than 37:00 hours
worked, so the
flexi is a minus time) also ned to be rolled over, so that the employee can
make up the time. Than i need to clear the cell at the end of the week
because if an employee has taken the the flexi then the cell needs to be
cleared, also if the flexi is not taken due to not having any flexi to take
(worked less than 37:00 hours) or out of personal choice than the flexi rolls
over either way as i have explained above, so again the cell will need to be
cleared, SO what ever happens with the flexi i will need to clear the cell
with some code in the final part of the formulae i am having trouble with.

These are the attempts that i have made with no luck;

=IF(AND(E10="F",H400),H36+H40,H40="")

=IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm"))

=IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm"))

Any guidence would be greatly appreciated.

Hi Joel,

Thank you for your input it was of help. The problem is that i understand
what you are trying to explain, but my manager wants the data to be in the
format of [h]:mm, but i will just have to change the format back to number
so that i can apply what you have suggested. The only thing was that i was
trying to do what i explained in my first question that i posted, but it
seems it is not possible.

THANK YOU for all the information you have provided and the time you have
taken out of your schedule to reply to all my posts.

Kind Regards

Zaf

"Joel" wrote:

I don't know you experience so lets start at the beginning.

time in hours start at midnight with 0 = midnight. A day equals 1, so ecry
hour is 1/24, and every minute = 1/(24)(60), every second = 1/(24)(60)(60)

so 8:00 AM = 8/24
8:00 PM = 20/24

8:30 AM = 8/24 + 30/(24)(60) = 1/3 + 1/48 = 16/48 + 1/48 = 17/48 =
.3541.6666

Now a Date starts at Jan 1, 1900 with every day = 1 (just like time, I
wonder why?).

July 8,2008 = 39737. Excel stores the date as a number and if you format
the cell to date the date will appear. If you enter 39737 in a cell then
change the format to date you will get todays date

July 8,2008 at 8:30 AM = 39737 + .3541666 = 39737.3541666


Your probelm results from a time in hours being greter that 24. Excel
usually assumes any time without a date is Jan 1, 1900 at midnight. If your
total time is 36 hours excel things the time is really Jan 2, 1900 at 12:00
PM. Because 36 is really 1.5 days excel add 1.5 to Jan 1, 1900 and gets Jan
2.

My suggestions was to seperate the days from the hours.

Using INT will get the whole number of days from the time

Days = Int(1.54) = 1

Using Mod will get the fraction part of the day.

Fraction of Days = Mod(1.54,1) = .54

Convert fraction of days to hours you multiply by 24

Hours are
24 * .54 = 12.96


Using Int will seperate the whole hours from the fraction

full hours = Int(12.96) = 12
You could also use the hour(.54)

Using MOD will get the fraction of the hours

fraction of hours = Mod(12.96,1) = .96

Minutes = .96 * 60 = 57.6

Full Minutes = Int(57.6) = 57
or simply =minute(.54)

fraction of minute = Mod(57.6,1) = .6

Seconds = .6 * 60 = 36
or simply =second(.54)



"Zaf" wrote:

Hi,

I appreciate the comments, the thing is that when istarted the the timesheet
was already produced, but he formulaes were missing, so i was instructed to
keep the formatting the way it was and to work aroud it. I have already have
had to find a way to show negative time; i have suggested changing the
format, but was told NO. I understand that if converted to number than it
will be displayed as 1.54, but i still i little fuzzy with your other
suggesstion. PLEASE if you have any other suggestions then could you let me
know.

THANK YOU

Kind Regards
Zaf

"Joel" wrote:

the problem is the 37:00 is not really 37:00 but is storesd as 1.54 which is
the number of days. If you format the cell as a number instead of the
date/time you wil see this number. To get back to the number of hours use

= A1 * 24 (make sure the cell is formated a number and not time).

To get minutes, seconds, and hours

=60 * mod(A1 * 24,1) <=this is the number of minutes, fraction times 60
= 60 * (60 * mod(A1 * 24,1)) <= this is seconds
= int(A1) <=hours, whole number


"Zaf" wrote:

PLEASE IGNORE THE PREVIOUS MESSAGE POSTED BY MYSELF!

I am currently attempting to work out the flexi time earned and taken in a
week. The problem i am having is that if some one is taking flexi they would
have had to work more than 37:00 hours a week, so if they haven't the flexi
is a minus time. I need help with adding to times togeather in the format
[h]:mm only if two conditions are met which are that an "F" needs to placed
in a cell to confirm flexi being taken and one of the cells that need to be
added must be greater the 0:00. If flexi has been earned by working over
37:00 hours and not taken then it must role over to the following week, as
must the flexi hours that have not been earned (i.e less than 37:00 hours
worked, so the
flexi is a minus time) also ned to be rolled over, so that the employee can
make up the time. Than i need to clear the cell at the end of the week
because if an employee has taken the the flexi then the cell needs to be
cleared, also if the flexi is not taken due to not having any flexi to take
(worked less than 37:00 hours) or out of personal choice than the flexi rolls
over either way as i have explained above, so again the cell will need to be
cleared, SO what ever happens with the flexi i will need to clear the cell
with some code in the final part of the formulae i am having trouble with.

These are the attempts that i have made with no luck;

=IF(AND(E10="F",H400),H36+H40,H40="")

=IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm"))

=IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm"))

Any guidence would be greatly appreciated.

Here are two solutions. I think the second is better than the first.


DY = Int(Range("A1"))
HR = Hour(Range("A1"))
Min = Minute(Range("A1"))

Hours = 24 * DY + HR


'or
TextTime = Range("A1").Text
HR = Left(TextTime, InStr(TextTime, ":") - 1)
Min = Mid(TextTime, InStr(TextTime, ":") + 1)

"Zaf" wrote:

Hi Joel,

Thank you for your input it was of help. The problem is that i understand
what you are trying to explain, but my manager wants the data to be in the
format of [h]:mm, but i will just have to change the format back to number
so that i can apply what you have suggested. The only thing was that i was
trying to do what i explained in my first question that i posted, but it
seems it is not possible.

THANK YOU for all the information you have provided and the time you have
taken out of your schedule to reply to all my posts.

Kind Regards

Zaf

"Joel" wrote:

I don't know you experience so lets start at the beginning.

time in hours start at midnight with 0 = midnight. A day equals 1, so ecry
hour is 1/24, and every minute = 1/(24)(60), every second = 1/(24)(60)(60)

so 8:00 AM = 8/24
8:00 PM = 20/24

8:30 AM = 8/24 + 30/(24)(60) = 1/3 + 1/48 = 16/48 + 1/48 = 17/48 =
.3541.6666

Now a Date starts at Jan 1, 1900 with every day = 1 (just like time, I
wonder why?).

July 8,2008 = 39737. Excel stores the date as a number and if you format
the cell to date the date will appear. If you enter 39737 in a cell then
change the format to date you will get todays date

July 8,2008 at 8:30 AM = 39737 + .3541666 = 39737.3541666


Your probelm results from a time in hours being greter that 24. Excel
usually assumes any time without a date is Jan 1, 1900 at midnight. If your
total time is 36 hours excel things the time is really Jan 2, 1900 at 12:00
PM. Because 36 is really 1.5 days excel add 1.5 to Jan 1, 1900 and gets Jan
2.

My suggestions was to seperate the days from the hours.

Using INT will get the whole number of days from the time

Days = Int(1.54) = 1

Using Mod will get the fraction part of the day.

Fraction of Days = Mod(1.54,1) = .54

Convert fraction of days to hours you multiply by 24

Hours are
24 * .54 = 12.96


Using Int will seperate the whole hours from the fraction

full hours = Int(12.96) = 12
You could also use the hour(.54)

Using MOD will get the fraction of the hours

fraction of hours = Mod(12.96,1) = .96

Minutes = .96 * 60 = 57.6

Full Minutes = Int(57.6) = 57
or simply =minute(.54)

fraction of minute = Mod(57.6,1) = .6

Seconds = .6 * 60 = 36
or simply =second(.54)



"Zaf" wrote:

Hi,

I appreciate the comments, the thing is that when istarted the the timesheet
was already produced, but he formulaes were missing, so i was instructed to
keep the formatting the way it was and to work aroud it. I have already have
had to find a way to show negative time; i have suggested changing the
format, but was told NO. I understand that if converted to number than it
will be displayed as 1.54, but i still i little fuzzy with your other
suggesstion. PLEASE if you have any other suggestions then could you let me
know.

THANK YOU

Kind Regards
Zaf

"Joel" wrote:

the problem is the 37:00 is not really 37:00 but is storesd as 1.54 which is
the number of days. If you format the cell as a number instead of the
date/time you wil see this number. To get back to the number of hours use

= A1 * 24 (make sure the cell is formated a number and not time).

To get minutes, seconds, and hours

=60 * mod(A1 * 24,1) <=this is the number of minutes, fraction times 60
= 60 * (60 * mod(A1 * 24,1)) <= this is seconds
= int(A1) <=hours, whole number


"Zaf" wrote:

PLEASE IGNORE THE PREVIOUS MESSAGE POSTED BY MYSELF!

I am currently attempting to work out the flexi time earned and taken in a
week. The problem i am having is that if some one is taking flexi they would
have had to work more than 37:00 hours a week, so if they haven't the flexi
is a minus time. I need help with adding to times togeather in the format
[h]:mm only if two conditions are met which are that an "F" needs to placed
in a cell to confirm flexi being taken and one of the cells that need to be
added must be greater the 0:00. If flexi has been earned by working over
37:00 hours and not taken then it must role over to the following week, as
must the flexi hours that have not been earned (i.e less than 37:00 hours
worked, so the
flexi is a minus time) also ned to be rolled over, so that the employee can
make up the time. Than i need to clear the cell at the end of the week
because if an employee has taken the the flexi then the cell needs to be
cleared, also if the flexi is not taken due to not having any flexi to take
(worked less than 37:00 hours) or out of personal choice than the flexi rolls
over either way as i have explained above, so again the cell will need to be
cleared, SO what ever happens with the flexi i will need to clear the cell
with some code in the final part of the formulae i am having trouble with.

These are the attempts that i have made with no luck;

=IF(AND(E10="F",H400),H36+H40,H40="")

=IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm"))

=IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm"))

Any guidence would be greatly appreciated.

Hi Fella,

Thanks again, i have just seen your post and will be attempting to implement
your second solution on Monday morning; i will let you know of any further
developments.

Kind Regards

Zaf

"Joel" wrote:

Here are two solutions. I think the second is better than the first.


DY = Int(Range("A1"))
HR = Hour(Range("A1"))
Min = Minute(Range("A1"))

Hours = 24 * DY + HR


'or
TextTime = Range("A1").Text
HR = Left(TextTime, InStr(TextTime, ":") - 1)
Min = Mid(TextTime, InStr(TextTime, ":") + 1)

"Zaf" wrote:

Hi Joel,

Thank you for your input it was of help. The problem is that i understand
what you are trying to explain, but my manager wants the data to be in the
format of [h]:mm, but i will just have to change the format back to number
so that i can apply what you have suggested. The only thing was that i was
trying to do what i explained in my first question that i posted, but it
seems it is not possible.

THANK YOU for all the information you have provided and the time you have
taken out of your schedule to reply to all my posts.

Kind Regards

Zaf

"Joel" wrote:

I don't know you experience so lets start at the beginning.

time in hours start at midnight with 0 = midnight. A day equals 1, so ecry
hour is 1/24, and every minute = 1/(24)(60), every second = 1/(24)(60)(60)

so 8:00 AM = 8/24
8:00 PM = 20/24

8:30 AM = 8/24 + 30/(24)(60) = 1/3 + 1/48 = 16/48 + 1/48 = 17/48 =
.3541.6666

Now a Date starts at Jan 1, 1900 with every day = 1 (just like time, I
wonder why?).

July 8,2008 = 39737. Excel stores the date as a number and if you format
the cell to date the date will appear. If you enter 39737 in a cell then
change the format to date you will get todays date

July 8,2008 at 8:30 AM = 39737 + .3541666 = 39737.3541666


Your probelm results from a time in hours being greter that 24. Excel
usually assumes any time without a date is Jan 1, 1900 at midnight. If your
total time is 36 hours excel things the time is really Jan 2, 1900 at 12:00
PM. Because 36 is really 1.5 days excel add 1.5 to Jan 1, 1900 and gets Jan
2.

My suggestions was to seperate the days from the hours.

Using INT will get the whole number of days from the time

Days = Int(1.54) = 1

Using Mod will get the fraction part of the day.

Fraction of Days = Mod(1.54,1) = .54

Convert fraction of days to hours you multiply by 24

Hours are
24 * .54 = 12.96


Using Int will seperate the whole hours from the fraction

full hours = Int(12.96) = 12
You could also use the hour(.54)

Using MOD will get the fraction of the hours

fraction of hours = Mod(12.96,1) = .96

Minutes = .96 * 60 = 57.6

Full Minutes = Int(57.6) = 57
or simply =minute(.54)

fraction of minute = Mod(57.6,1) = .6

Seconds = .6 * 60 = 36
or simply =second(.54)



"Zaf" wrote:

Hi,

I appreciate the comments, the thing is that when istarted the the timesheet
was already produced, but he formulaes were missing, so i was instructed to
keep the formatting the way it was and to work aroud it. I have already have
had to find a way to show negative time; i have suggested changing the
format, but was told NO. I understand that if converted to number than it
will be displayed as 1.54, but i still i little fuzzy with your other
suggesstion. PLEASE if you have any other suggestions then could you let me
know.

THANK YOU

Kind Regards
Zaf

"Joel" wrote:

the problem is the 37:00 is not really 37:00 but is storesd as 1.54 which is
the number of days. If you format the cell as a number instead of the
date/time you wil see this number. To get back to the number of hours use

= A1 * 24 (make sure the cell is formated a number and not time).

To get minutes, seconds, and hours

=60 * mod(A1 * 24,1) <=this is the number of minutes, fraction times 60
= 60 * (60 * mod(A1 * 24,1)) <= this is seconds
= int(A1) <=hours, whole number


"Zaf" wrote:

PLEASE IGNORE THE PREVIOUS MESSAGE POSTED BY MYSELF!

I am currently attempting to work out the flexi time earned and taken in a
week. The problem i am having is that if some one is taking flexi they would
have had to work more than 37:00 hours a week, so if they haven't the flexi
is a minus time. I need help with adding to times togeather in the format
[h]:mm only if two conditions are met which are that an "F" needs to placed
in a cell to confirm flexi being taken and one of the cells that need to be
added must be greater the 0:00. If flexi has been earned by working over
37:00 hours and not taken then it must role over to the following week, as
must the flexi hours that have not been earned (i.e less than 37:00 hours
worked, so the
flexi is a minus time) also ned to be rolled over, so that the employee can
make up the time. Than i need to clear the cell at the end of the week
because if an employee has taken the the flexi then the cell needs to be
cleared, also if the flexi is not taken due to not having any flexi to take
(worked less than 37:00 hours) or out of personal choice than the flexi rolls
over either way as i have explained above, so again the cell will need to be
cleared, SO what ever happens with the flexi i will need to clear the cell
with some code in the final part of the formulae i am having trouble with.

These are the attempts that i have made with no luck;

=IF(AND(E10="F",H400),H36+H40,H40="")

=IF(E10="F",H36+H40,TEXT(H40,"-[h]:mm"))

=IF(E10:J10="F",TEXT(H36+H40,"[h]:mm"),TEXT(H40+I40,"[h]:mm"))

Any guidence would be greatly appreciated.